Help with Dynamic Validation Lists / Drop Downs / dynamic filters

[rjnc]

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!

[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

[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

[rjnc]

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?

[rjnc]

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.

[Ochimus]

rjnc,

Explanation clarified everything.

Attached rework does what you need.

Top of sheet shows which letters are linked to which, and how they cascade to next level.

So in Box 1 you choose A or B (as before), which then sets the contents of DropBox 2.

If you chose "a" originally, DropBox 2 will contain c - f.

Cols C - F then show you what you get against each of those second choices.

If you chose "b", DropBox2 will contain g - l, and Cols I - N show those choices.

If you click on Name Manager under Formula. you will see every column is set as a "Named" range.

Each dependent DropList can therefore use =Indirect("C. . "), meaning it looks in Row 2 for whatever letter is in the DropList above, and adds the letters in that "range".

Ochimus