Dynamic Multi-level Cascading List Data Validation From Large Table

[apettis]

I've done this easily with two or so levels using the common indirect method, but I'm struggling to apply that knowledge to a greater challenge. Here's what I have:

I have a table set up with the columns Room, Option, Style, and Color. I have another sheet where our customer will make selections from multiple dropdowns for each room: Foyer, Living, Dining, etc. - Those rooms are in a column already. Each room has different options (vinyl, hardwood, and sometimes carpet), each of those has different styles (plank or tile, etc.), and each of those has different colors.

The extra challenges I'm struggling with:

Having all of my source data in one table and using sequentially more-restricted ranges verses having to create copious amounts of lists - and keeping it dynamic. The example I gave above is only the beginning of the work I'm setting out to do. I'd like to be able to continue adding more selection options to the table and the dropdown lists adjust accordingly.

Do I *have* to commit to creating separate lists for everything? Thanks. =)

 

[RasGhul]

If you're returning multiple values from criteria across # columns, you could start looking at concatenating the selections versus table. This reduces how many criteria sections need to be included in the array formula.