Workaround for Cascading Data Validation with Dynamic named ranges!!!!!

[magicbob007]

Hi Guys,

So I have been searching for a solution for this problem which is that if you want to cascade data validation drop down lists you cannot do it with dynamically named ranges due to the fact that the INDIRECT function is not compatible with formulas. However, this weekend while trying running through some possible solutions I had a revelation! My "ah, HA!" moment if you will...

Here is a super SIMPLE workaround that allows dynamic named ranges without any formulas and so you can cascade the DV drop downs and still keep lists that self update.

What you do is organize all your DV lists into individual tables and name each table something like "Table(name)" and make sure it has a header. Then go and name a range after the table header and define its cells like so: "Table(name)[header]" and BINGO! You now can use the INDIRECT function with the names of the headers to cascade your drop down lists. Since all the data lists are in tables as long as you add your items to each list without a space in the rows the table will automatically adjust to include the new item!

See the attached excel file for a more clear explanation!

Sorry, if this has been discovered and explained before, but I have not been able to find it on this forum or on any Google search I have run so I thought I would share it. Considering how many posts there are on this forum and on countless other websites about how to do this I thought people might find it useful.

Hope this helps since this is my first post of my own solution to a problem so let me know what you guys think and add to my reputation if you like it!

Magicbob007

[Haseeb Avarakkan]

Hello Magicbob007,

I have converted the Table to normal range. You can add/delete heading in Horizontal, add/delete entries in each heading. So this will automatically update. Not necessary to define a name for each category.

See the attached.

[magicbob007]

that does work perfectly also. Another way I found to working around it was to define an function in a named range to take the place of the indirect function in the cascading DV. so named a range DINDIRECT (for dynamic indirect) and then the refers to was "=EVALUATE(INDIRECT("RC[-1]",0))"

then anywhere I would have used indirect, I put DINDIRECT. The limitation being that it only refers to the cell on its left.

[Whizbang]

See the attached workbook on how to do cascading dynamic dropdowns using INDEX.

Also, read this article to understand better the INDEX function:
http://www.excelhero.com/blog/2011/0...ing-index.html

[magicbob007]

I while I do not fully understand the power of the INDEX function, I do see the merit of using it in larger data sets for its speed. However, I think setting up the tables is still the simplest solution when thinking about trouble shooting and the end user. Both of these other solutions are perfect and function as I would have needed, but the functions that power them are quite complex, utilizing arrays and INDEXing. It would be impossible for me to explain these to my coworkers, let alone have them apply them on their own.

[mtpyankee]

Haseeb,

Haseeb A: Could add another dimension to your example if you get this reply where it has another variable, such as color, it would greatly help me and my project. So it would cascade:

Company
Car
Color

and car is dependent on company; color dependent on car.

Thanks