This post will tell you how to create dynamic dropdowns in excel based on a cell’s value.
The example we have taken here is, based on the country selected in the source cell, the dropdown will show the city names in the destination cell. It also covers, how to create a dropdown from the range of values.
Now let us create a dropdown on the D8 cell (next to Select the country).
1) Go to the Data ribbon >>> Click the Data Validation button as shown below
2) Now, Data Validation Box will appear, In the settings tab,validation criteria >> Allow “List”
3) Now specify the range with values that should come in the dropdown on the cell D8 and click “Ok” as shown below.
4) This will give the dropdown with the country names as shown below.
5) Now, based on the country selected the city names has to be populated in D9. For which, select the “D9” cell, Go to Data Validation in the Data ribbon >> select select the list as shown above.
6) Type the formula
“=IF($D$8=$A$3,$B$3:$E$3,IF($D$8=$A$4,$B$4:$E$4,$B$5:$E$5))“
in the range textbox and click ok.
7) So, you will get the desired choices as shown below.
Hope it helps.