So previously I wrote about how you can create a simple dropdown list in Excel, which is perfect for any kind of data validation. If you have a range of values that people are required to enter repeatedly, it’s best to create a dropdown list so that no invalid data can be entered.
That’s all great, but what if you need a linked dropdown list? What do I mean? For example, someone choose a value in dropdown list A and you want the values to be updated in dropdown list B.
Let’s start with an example to better understand how we can use linked lists in Excel. Let’s say I have a table that has three columns that represent the brands of a car: Toyota, Honda and Nissan. The rows represent the models:
So what we could do is have one dropdown that contained the brands, such as Toyota, Honda and Nissan and the second dropdown would have the models. If we were to select Toyota, the second dropdown would show Prius, Camry, and Solara.
So in order to do this, setup the data in a table like show above. Now select the categories (Row 1) and give the range a name in the upper left text box above column A.
Now that you named the range for the categories, you need to name the range for each of the options as shown below:
Now to create the first dropdown list, you need to click on any blank cell and then click on the Data ribbon and click on Data Validation.
Now choose List for the Allow box and type in “=carbrands” into the Source box. Note that you would type in whatever you named your categories, not necessarily “carbrands”.
Click OK and your first drop down list will appear. Now make sure to note down which cell you put the first dropdown list in because you will need it later on, i.e. E2, etc.
Now, click on another blank cell and go to Data Validation again. Choose List again, but this time type in “=indirect(E2)” into the Source. Instead of E2, you will put in the cell location for the first dropdown list you created.
The indirect function will basically take the choice you made in the first dropdown and use it for the second one. Note that if you get a message like “The source currently has an error, do you want to continue”, it means that you have not yet chosen a value for the first dropdown (the NULL value cannot be selected).
That’s it! Pretty cool eh!? You can continue going deeper if you like, just use the second dropdown as the Source of the third dropdown. Enjoy!
Thanks for the great suggestion.
Are you able to explain how you would add a third level of categorisation e.g. If I select Honda, then select Civic how could I then select e.g. Colour Red, Blue, Green?