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

dependent dropdown boxes

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.

dependent dropdown boxes

The options in the initial list then should be the headings for the dependant lists.

dependent dropdown boxes

Using a blank cell and selecting the data validation options on the data ribbon

dependent dropdown boxes

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.

dependent dropdown boxes

With the first drop down box now complete we need to highlight a second cell in order to setup the dependant drop down box.

dependent dropdown boxes

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.