Dependent dropdown lists
We have already seen how to make a single data validated drop down list, the progression here is to have a second dependent dropdown list that is driven by the selection in the first box.
In order to have a dependant second cell we need a second (or multiple) list(s).
The “Favourite animal” list has three different animals, with lists 2 to 4 breaking down each option further.
Best practise is to name each range of data by selecting the data then altering the name in the top left hand corner.
The options in the initial list then should be the headings for the dependant lists.
Using a blank cell and selecting the data validation options on the data ribbon
On the data validation pop up box select “List” in the allow option box and the source should equal the name range of the first list. In this case the range is animal.
With the first drop down box now complete we need to highlight a second cell in order to setup the dependant drop down box.
The Source of “=INDIRECT(B7)” tells Excel that an exact source is not provided but that the function should be based on the name range of the first drop down.