Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    56
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Adding conditional combo box to spreadsheet

    I need to add a combo box to a cell to provide users ‘Details Desc’ choices based on the ‘Details Code’ in an adjoining cell. The ‘Details Code’ and ‘Details Desc’ lists are in a separate worksheet called ‘DropDowns’. So, if C6 = 7400 list the 7400 choices in the D6 dropdown.

    I haven’t had much experience with development in Excel so I’m rather lost.

    Example Data:

    Details Details
    Code Desc
    7400 Committee Meeting
    7400 DQR
    7110 CDL
    7110 Certification
    7210 Member Planning
    7210 P&M

    Appreciate the help.

    Sue

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 340 Times in 333 Posts
    I am not sure exactly what you are after. Are you looking to create dependent data validation lists like in: http://www.contextures.com/xlDataVal02.html

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    RetiredGeek (2012-01-30)

  4. #3
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    56
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi Steve... yes dependent validation lists is what I'm looking to do. However I worked through the example you referred to and got the first dropdown list to work but after numerous attempts I could not get the second list box to show any data.

    I've attached a copy of the workbook, if you wouldn't mind taking a look. The orange shaded fields on the 'Input Sheet' worksheet is where I want the data to be selected from and the dropdown lists for the 'Details Code Desc' field are on the 'DropDown' worksheet.

    This seems pretty straight forward, but I just can't figure out what I'm doing wrong.
    Attached Files Attached Files

  5. #4
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,485
    Thanks
    26
    Thanked 171 Times in 167 Posts
    Hi

    You nearly had it.
    See attached file.

    First, you need to have range names assigned for the relevant lists.
    On sheet [DropDowns], I manually assigned the names from the headings to the blocks underneath, e.g. Details7110, Details7210, Details7400 etc. (I colour-coded the blocks to show what ranges had been assigned to these names).

    Now, you just amend the data source in your Data Validation settings to refer to these names like this:
    In cell [D6], the source for the Data Validation settings is:
    =INDIRECT("Details"&C6)

    Because you have defined the C6 in this formula as 'relative' rather than 'absolute' (i.e not as $C$6), you can now simply Copy the cell [D6] to cells [D10], [D14], [D18] etc (I have done this in the attached file.

    zeddy
    Attached Files Attached Files

  6. The Following User Says Thank You to zeddy For This Useful Post:

    SueP (2012-02-03)

  7. #5
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    56
    Thanks
    4
    Thanked 0 Times in 0 Posts
    If I was doing this in MSAccess it would have been done ages ago. Not much of an excel user.

    Thank you so much for responding so quickly.
    Sue

  8. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,485
    Thanks
    26
    Thanked 171 Times in 167 Posts
    Hi Sue
    First of all let me say I love dropdown lists (they help to eliminate many data entry errors).
    I especially love conditional-dropdown lists (your list choices are restricted by previous choices made).

    However, you need to be aware of certain things if you want to rely on 'correct' selections being made by other Users.
    For example, in your Expense report Input Sheet, a User cannot make a dropdown choice for the Code Description in column [D] until(and unless) a selection has made from the dropdown alongside in the Code cell (in column [C]).
    This is good so far, i.e if you click dropdown for the Code Description there will be an 'empty list' and nothing to select unless a Code has been chosen first.
    So the User must always select a Code first.

    But after making a selection for Code (from the Code dropdown), and then a selection from the Code Description dropdown, there is nothing to stop the User from going back to the Code dropdown and changing the selected Code.
    You will not see 'anything wrong'.

    Excel has made provision for sitiuations like this by giving you a 'Circle Invalid Entries' feature.
    In Excel2010 this is found in the top panel Ribbon interface by clicking on the [Data] tab, then displaying the [Data Validation] dropdown menu (by clicking the blob next to the word, not the word itself).
    This will then reveal the additional options:
    [Circle Invalid Data]
    [Clear Validation Circles]
    (For other Excel versions its in View > Toolbars > Formula Auditing )
    Clicking [Circle Invalid Data] will place a red circle around any cell that has an entry that doesn't meet the dropdown rule.
    There are other ways of 'breaking' a dropdown validation rule: For example, copy a cell containing the word 'banana' with [Ctrl-C] and then paste it on a dropdown cell using [Ctrl-V] to bypass the validation rules.
    These will also be picked up with the [Circle Invalid Data] feature.

    It is possible to create multiple-conditional-dropdowns which correctly enforce valid combinations of dropdowns but this involves VBA.

    I suppose there is always Access as well, but then not every company has Access available on all PCs, whereas Excel is everywhere.

    zeddy

  9. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    hasse (2012-05-10),ruosChalet (2012-02-11)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •