Make a Data Validation list Dynamically

jambar

New Member
Joined
Jul 24, 2010
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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 .

1702536731904.png
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Well I have managed for the 1st two options as follows

I set the information as a table

1702556601623.png


Then I set a unique filter for the code
1702556661531.png


Then underneath each i set another filer

1702556733064.png



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

1st drop down is simply
1702557708599.png


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)))
1702557817153.png


and thats as far as I have got.
 
Upvote 0
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)))
1702559019746.png


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


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.
1702559329371.png


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

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top