Therefore, I wrote earlier about how you can create a simple drop-down list in Excel, which is ideal for validating any data. If you have a range of values ??that people need to re-enter, it is best to create a drop-down list so that you cannot enter invalid data.
Let’s start with an example to better understand how we can use linked lists in Excel. Let’s say I have a table with three columns that represent the brands of a car: Toyota, Honda, and Nissan. The strings represent the models:
So what we could do is create one dropdown menu containing brands like Toyota, Honda and Nissan, and the second dropdown contains models. If we had chosen Toyota, the second dropdown would show the Prius, Camry and Solara.
To do this, set up the data in the table as shown 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 have named the range for the categories, you need to name the range for each of the options as shown below:
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 select “List” in the “Allow” field and enter “= carbrands” in the “Source” field. Please note that you must enter whatever you have named for your categories, not necessarily “car brands.”
Click OK and your first dropdown will appear. Now, be sure to write down which cell you put the first dropdown in, because you will need it later, like E2, etc.
Now click another blank cell and go to data validation again. Select List again, but this time enter “= Indirect (E2)” in the source. Instead of E2, you will place a cell for the first dropdown you created.
The indirect function will basically take the selection you made in the first dropdown and use it for the second. Please note that if you receive a message like â€œThe source currently has an error, you want to continueâ€, this means that you have not yet selected a value for the first drop-down list (you cannot select NULL).
This is it! Pretty cool, right? You can go deeper if you like, just use the second dropdown menu as a source for the third dropdown and so on. Enjoy!