Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cascading Combo Box help? (Excel 2004)

    Hi,

    I'm trying to figure out how to make cascading combo boxes in Excel, ie the list in combo box 2 is dependent on the selection in combo box 1. In theory, it wouldnt seem too challenging, but the reading I have done hasnt helped any. I really need to see one in use.

    Might anyone be able to set up a simple one and attach it so I could have a look at it and figure out how to edit/create them as I need? Any help appreciated. : )

    ~Aera

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading Combo Box help? (Excel 2004)

    Good morning

    I have attached a sample that I downloaded some time ago, I am afraid I have forgotten from where so I cannot offer the proper acknowledgment, hope it helps you

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cascading Combo Box help? (Excel 2004)

    I suspect that you may have gotten this technique from MS MVP Debra Dagliesh's page Excel -- Data Validation -- Dependent Dropdowns from a Sorted List

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading Combo Box help? (Excel 2004)

    Thanks Steve

    you are probably right, I use that site a lot for visual references and do try to acknowledge Debra whenever I I cite any of her stuff, having been up until 0130 trying to get a FindPrevious function working my brain was not in gear at 6(ish) when I got up for work this morning when I posted this answer. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    New Lounger
    Join Date
    Jan 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading Combo Box help? (Excel 2004)

    Thanks Steve. That helped a lot. Its close to what I want to do. I also found the file from a free download site that I have attached. I like it a little better than the one you posted, as the combobox info automatically updates in that one. However, in each case, the combobox data is on the same sheet as the comboboxes. That would be too cumbersome and I dont want to do that.

    I'll show you what I tried in a follow-up post with my attachment there. It isnt working (lol) though both this file and the one you attached I understand and could implement.
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Jan 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading Combo Box help? (Excel 2004)

    Here's my humble test file. As you can see it doesnt work (the second combobox doesnt have its range info). I used the same sort of data just for example as the attachment I posted above, except I put the list data on individual sheets. What I cannot figure out how to do is how to have combobox 2 refer to the different sheets (I get errors when I try to insert Months! for example in some string, in addition to the range numbers for row). In H2 and J1 of mine I was trying to get a return value that I could use for that. I'm sort of stuck.

    The end goal though is for combobox 2 to read from the correct sheet, and then in C1 and D1 place the appropriate value from said sheet (under Header 1 and Header2).

    Anyone have any thoughts on how to get at least the combobox part to work? Once I get that far I can probably get the correct data to show up in C1 and D1.

    Much appreciated.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading Combo Box help? (Excel 2004)

    Hi Aera

    I have tried to get this working from the examples but at my level of Excel I just cannot fathom it, I can get your second combo to show the months when month is selected in the first combo and in another cell I can get it to show some cell references when day or number is selected but other than that no use I am afraid, there are many loungers here who will find it easy though and will enlighten you through the day I am sure. I will keep trying as time allows

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Cascading Combo Box help? (Excel 2004)

    Is this the sort of thing you wanted?
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    New Lounger
    Join Date
    Jan 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading Combo Box help? (Excel 2004)

    Thanks for spending some time looking at it Steve. I really appreciate it.

    Rory, that's it exactly. Thanks so much. I can definitely work with this and am able to edit it as needed.

    I think I understand everything, except I'm not sure where the values in the defined name of "Range" under "Refers to" are coming from, namely ...(INDIRECT('J1'!J65529)... 'J1'! is the variable for setting the sheet to refer to that I was trying to figure out how to do. Where is it drawing its value from? J65529 is curious. I believe its referring to a range, as it does so in the original example, but here this value changes with a specific pattern each time I look at it under Define, depending which cell on Sheet1 I have selected when I go into Name. If its too much trouble to explain (too complicated, too long an answer), no problem at all. I'm just curious as I like figuring all the parts out of why it works the way it does. Everything else makes sense to me, so I may be able to sort this out by tinkering a bit.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascading Combo Box help? (Excel 2004)

    I think "Range" is a left-over; this name can be deleted without harm.
    The definition of "Range" uses a relative cell reference, so the definition you see in Insert | Name | Define depends on the active cell.

Posting Permissions

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