Make a Data Validation list Dynamically

[jambar]

I use MS Excel 2016 and want to set up data validation list based on the selection done on earlier cell/s .

I have lists in Column B, C and D of a table . Task to perform
1. Cell G4 - To get a drop down list (data validation) of Material codes (like IS1239, IS3589) for me to chose one. example IS3589

2. Cell H4 - To get a drop down list (data validation list example 7",8",10",12" 14",16",18") of Dia relevant to my selection in G4 only . so that I can select one dia . Example 10"

3. Cell I4 - To get a dropdown list (data validation list example 4.85, 6.35 ) of Thickness to my selection in H4only , so that I can select one thickness . Example 6.35

Please help me to do the above 3 tasks .

 

[Tom.Jones]

Look at: https://www.contextures.com/xldataval02.html#third

 

[nemmi69]

Look at: https://www.contextures.com/xldataval02.html#third

That approach only works if its 1 line . He has 6 lines so all of it would have to be done 6 times. I haven't fond an easy option yet

 

[Peter_SSs]

Before I rush in to this version, I'm wondering how the previous version of DV worked out?

 

[nemmi69]

Well I have managed for the 1st two options as follows

I set the information as a table

Then I set a unique filter for the code

Then underneath each i set another filer

Then as drop downs do not like table references I created a named range MatCode=Materials[Material Code]

1st drop down is simply

The 2nd drop down is set by if statement in a list
=IF($L2=$F$1,$F$2:$F$20,IF($L2=$G$1,$G$2:$G$20,IF($L2=$H$1,$H$2:$H$20)))

and thats as far as I have got.

 

[nemmi69]

Continuing on from before but I moved over the selection boxes and increased to 5

So 2nd down is set by if statement in a list
=IF($Q2=$F$1,$F$2:$F$20,IF($Q2=$G$1,$G$2:$G$20,IF($Q2=$H$1,$H$2:$H$20)))

To get 3rd drop down
For each of the lines that can be selected (in this case 5) I generated a filter

So J1 formula refers to the result of 1st row 1st drop down (Q2) and the 2nd drop down (R2)
and repeats for the rest

3rd drop down is by straight reference allowing for increase in the number of values.

To reduce clutter once all setup E to P columns could be hidden.