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.
INDEX and MATCH Functions
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.
Data Entry Sheet Drop Downs
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.
Thanks for that Debra.
Really helpful for me right now.
Thanks to Roger too 🙂
Hi Lincoln
Glad it helped.
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!
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.
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.
@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.
=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.