Dynamic Dependent Data Validation Lists

Roger Govier has created a sample workbook and instructions that show you how to create dynamic dependent data validation lists. His technique lets you create multiple levels of dependent data validation, without defining a named range for each list. Instead, Roger's formula uses the INDEX function, with MATCH, to find a column heading, then shows the list from that column.
For example, on one sheet you can list regions, countries, areas and cities.
Then, on another sheet, select a region in column A, and see only the related countries in column B's data validation drop down. In column C, you'd see only the areas in the selected column, then only the applicable cities in column D.

How It Works

With Roger's technique, you'll create four defined ranges, then use two of those ranges as the source for data validation drop down lists. The ranges are dynamic, so you can add more items to any list, or add new lists, and the defined ranges will automatically adjust.
There are full instructions for Data Validation – Dynamic Dependent Lists, and a sample workbook that you can download. Also, Roger Govier's contact information is at the bottom of that page.

0 thoughts on “Dynamic Dependent Data Validation Lists”

  1. Hi Roger,
    Thanks for your helpful post. I need one more layer of functionality, and I'm hoping you can help! I only need two lists, but I need to allow users to list more than one selection at each level. So for example, the spreadsheet would look like:
    A1: Americas
    B1: US
    B2: Mexico
    B3: Canada
    A4: Europe
    B4: UK
    B5: France
    B6: Germany
    Can you help?

  2. This site has some great information, what do I do if I my list is vertical and not horizontal, like the examples above? Named ranges are more than likely not possible.
    Given the vehicle information below, I would like to be able to have the process look like:
    A B C
    1 Select Vehicle Select Engine Select Options
    2 67-69 AMC AMBASSADOR 4.8L(290) V8 w/55A Motorola
    3 67-69 AMC AMBASSADOR 4.8L(290) V8 w/55A Motorola
    4 67-69 AMC AMBASSADOR 4.8L(290) V8 w/37A Motorola
    5 67-69 AMC AMBASSADOR 5.6L(343) V8 w/55A Motorola
    6 67-69 AMC AMBASSADOR 5.6L(343) V8 w/37A Motorola
    7 1998 Acura NSX 3.0L -3196cc All
    Type into my tracking sheet:
    A B C
    Click on first box and see:
    A B C
    A17: 67-69 AMC AMBASSADOR | 4.8L(290) V8 / 5.6L(343) V8 | (EMPTY DROP BOX)
    After selecting, click on second box and see:
    A B C
    A17: 67-69 AMC AMBASSADOR 5.6L(343) V8 | W/55A Motorola / W/37A Motorola |
    With the final row to look like:
    A B C
    A17:67-69 AMC AMBASSADOR 5.6L(343) V8 W/55A Motorola
    Is this possible without VBA? Or will it require VBA? I'm starting to find out that I will require VBA.

  3. @Colin. If you're using Excel 2010, then this should do the trick:
    (You'll have to download...it won't work in browser)
    The key is to treat this as two different Dynamic Dependent Data Validation Lists:
    1. a "Vehicle to Engine list"
    2. a "Engine to Options list"
    Basically the approach is the same as above, although mine uses Excel 2010 tables to make the matching formula a bit simpler. I can rework it for earlier versions of Excel if you like.

  4. =OFFSET('Data Validation'!$DD$3,0,0,COUNTIF('Data Validation'!$DD:$DD,"?*")-1,1)
    For ignoring empty cells which contains formulas in the list AND shows the values from beginning in data validation cell.

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.