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.

RegionLists

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.

RJG_DataValIndex

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.

Tech4ULogo

____________________

You may also like...

8 Responses

  1. Lincoln says:

    Thanks for that Debra.

    Really helpful for me right now.

  2. Lincoln says:

    Thanks to Roger too :)

  3. Roger Govier says:

    Hi Lincoln

    Glad it helped.

  4. Kate says:

    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?

    Thanks!

  5. Jeff Weir says:

    THat’s a neat trick! I’ve missed this until now, and so have been using some pretty complicated VBA instead. I’ll be stripping that VBA out tomorrow!

    Out of interest, I just fourd a good writeup at http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/ that uses Excel’s table functionality to make dynamic dependent list.

    Cheers Roger.

  6. Colin says:

    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
    A17 67-69 AMC AMBASSADOR (EMPTY DROP BOX) (EMPTY DROP BOX)

    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.

  7. Jeff Weir says:

    @Colin. If you’re using Excel 2010, then this should do the trick:
    https://docs.google.com/open?id=0B1hgC5lSuLjVcExDcUJEVDdSSzA

    (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.

  8. sunil says:

    =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 sunil Cancel reply

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