Dynamic dependent data validation lists

**gak67** · 05-20-2014, 05:08 PM

Hi

I thought I knew Excel fairly well until I came on here, and I've been able to help a few people since I became a member, but I have learnt so much more than I have taught, so thanks.

Now I have an issue I'm hoping you can help with. I was going to post a reply to this post http://www.excelforum.com/excel-form...down-menu.html and tell the OP he could use a dynamic named range since he said

the list is almost 200 cells long and growing

The problem is that when I tried to test it, it didn't work. I created the dynamic named range with the following formula:

Formula:

=OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C:$C)-1,1)

When I referenced the result from the first drop down list with

Formula:

=INDIRECT($A$1)

it came up with an error saying The Source currently evaluates to an error. Do you want to continue?

I have attached the file I created, so any insights or assistance would be gratefully appreciated.
Dynamic Dependent Ranges.xlsx

Drop down lists should be in A1 and A2

gak67

**JBeaucaire** · 05-20-2014, 05:52 PM

'Dynamic Cascading Dependent Drop Boxes
Typically Dynamic Named Ranges and Cascading Dependent Drop Down boxes using INDIRECT() references to prior DV cells simply does not work. You can do one or the other, but not both. Here is a technique for creating cascading dependent drop boxes where the secondary lists ARE created dynamically, compatible with Excel 2003+.
Dynamic Cascading Data Validation

There is a sample workbook showing it in action on that page.

I've implemented the technique into your sample.