Results 1 to 6 of 6

Thread: Drop down lists

  1. #1
    Join Date
    Feb 2005

    Question Unanswered: Drop down lists

    Excel Question: How do you make choices in one drop down list dependant on choices in another drop down list?

    Example: Say you are buying computers; you have a choice of “Computer 1” or “Computer 2”. “Computer 1” only has a choice of a mouse or
    Smart Card reader, “Computer 2” has a choice of a DVD-ROM or 17” Monitor.
    In the first drop down menu, your choices of “Computer 1” or “Computer 2” appear. This choice determines whether the options of Mouse/Smart Card, or DVD-ROM/17” Monitor appear in the second drop down box.

  2. #2
    Join Date
    Oct 2003
    Perhaps the best explanation of how to do this is Aladin's:

    Dependent Dropdown Lists
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jul 2012
    I track requests on vehicles and locations for my company.

    Our website allows customers selections with drop boxes to send requests. This cascading drop boxes is what I would like to do in my tracking Excel sheet.

    On my sheet I've combined the Year, Make, Model information already and only need selectable options for Engine and Options. By recreating this drop down function in my Excel sheet it will help expedite the process for me and allow me to spend time being productive in other areas.

    I've been researching cascading dependent drop boxes for a while now. I find many examples of how to do this (states, fruit, etc.). The issue that I am having is my information is found in column format, not in rows that many of those examples utilize. It is also ~23000 rows of vehicle information.

    Given the vehicle information below, I would like to be able to have the process look like:
    Select Vehicle Select Engine Select Options
    67-69 AMC AMBASSADOR 4.8L(290) V8 w/55A Motorola
    67-69 AMC AMBASSADOR 4.8L(290) V8 w/55A Motorola
    67-69 AMC AMBASSADOR 4.8L(290) V8 w/37A Motorola
    67-69 AMC AMBASSADOR 5.6L(343) V8 w/55A Motorola
    67-69 AMC AMBASSADOR 5.6L(343) V8 w/37A Motorola

    Type into my tracking sheet:

    Click on first box and see:
    67-69 AMC AMBASSADOR | 4.8L(290) V8 / 5.6L(343) V8 | (EMPTY DROP BOX)

    After selecting, click on second box and see:
    67-69 AMC AMBASSADOR 5.6L(343) V8 | W/55A Motorola / W/37A Motorola |

    With the final row to look like:
    67-69 AMC AMBASSADOR 5.6L(343) V8 W/55A Motorola

    Will something like this require VBA? Or can I use a drop box with a formula to accomplish this? Or is it even possible to do?

  4. #4
    Join Date
    Jul 2012
    The more I've looked around I'm starting to feel that VBA will be the way I can do what I am looking for. However I have no knowledge of VBA.

    Can anyone speak on how the website is able to narrow down the sources from the excel without having any named ranges or anything.

  5. #5
    Join Date
    Oct 2012
    Instead of using a List Box from data validation, you could try creating a combobox (from the developer tools).
    Within that you can build your initial list and then do a series of lookups that fills out a range and then put that range into a List Box.

    You create your initial list in Column M (M1:M9) 8 values
    Drag a Combo box in
    Rightclick format control and put in the range and select were you want the linked cell to be (this will list the number associated with that entry from M1:M9)

    You can then create another lookup to lookup this value and link another combo box to that list.

    Hope that makes sense.

  6. #6
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    The website will probably work from a series of SQL statements, written such that the value in the previous one determines the subset of records selected for the current one. It's how this is achieved for the Access version of this functionality, anyway, and something similar works for Lotus Notes.

    Excel's named ranges work with data validation, but I have never seen a means of making this dynamic. If you wanted to use VBA to do so, you would need to use combo box controls rather than data validation, and you would need to write a script to pick up the value of one box and use it to determine the recordsource range for the next. It would be tricky, but doable.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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