Help with Dynamic Validation Lists / Drop Downs / dynamic filters

Hi All,

I have a 5 different variables that i want to build in to validation lists / drop downs.
The issue that i am facing is that certain dependent variables do not return results for the independent variable. Ideally the solution im looking for is for each of the lists to be dynamic, therefore only if a result is available that i want the validation list to only show those that have a result.

I have created a sheet to show how the lists work and the dependencies on each.

Any assistance would be great!

Originally Posted by Ochimus

Hopefully sheet 2 in the attached does what you want.

Cols A - E set the contents of the five initial DropLists as Ranges by clicking each selection in turn, clicking "Formula", choosing "Create from selection", and ticking "Top Row"

Cols G - P then lists the "Dependent" ranges as mapped in your Sheet 1. As an example, copying D3 to H4 on sheet 2, selecting Data and using "Text to Columns" created the list in H4 - O4.

You then repeat the Range Name process for each row, except in "Create from Selection", you tick "Left Column" instead of "Top Row"

Because the "Dependent" ranges are now linked to the contents of the five Drop Lists, you set the main Drop Lists in T2 - T10, and in U2 - U10 create subsidiary Data Validation as "indirect"

(e.g., U6 will be =indirect("T6") and only the letters linked to that Col T choice will appear in the Dependent drop box.

One thing I found unclear is Col B in the Sheet 1 structure?

Ranges c - l are colour-coded as Dependants of DropDown1, but the label sets them as a DropList in their own right.

But they then appear as Dependants of DropDown3 as well?

Hope that helps

Ochimus

Thanks Ochimus, sorry i wasnt more clear, basically what i want to achieve is, if i select b in the first drop down, i only want to see g / h / i / j / k / l, if i then select 'k' in the second drop down, i only want to see m / o / p in the 3rd drop down, if i then select 'p' in the 4th drop down, i only want to see x / y. If i select 'y' then the only option i want to see is ar / as. Does that make sense?

Originally Posted by Richard Buttrey

Hi,

For anything to do with dependent drop down lists I can do no better than refer you to Debra's site on this subject here

Thanks Richard, i will be sure to look at this.