Data Validation drop-down lists are a powerful Excel feature. Creating a list of items for the user to select from is not only an efficient way to input data into a cell but also ensures that the data going into the cell is the same every time, helping to keep the spreadsheet error-free and consistent.
In the above example, I have created a single data validation drop-down list to make it simple for anyone to select an app and input it into the cell.
However, what if you want to have multiple dependent drop-down lists? In the example below, the ‘Team Member’ drop-down list is dependent on the selection made in the ‘Department’ drop-down list. If I were to select ‘Sales’, the list of team members would update to show the team members in that particular team.
There are many ways to create dependent drop-down lists so let us look at a couple of them.
In this example, I am going to use the INDIRECT function to create two dynamic data validation drop-down lists. I am aiming to create a drop-down list in cell B14 that lists the countries and a drop-down list in cell B15 that lists the tours available in whichever country has been selected in cell B14.
To make this easier, I am going to create a named range for ‘Countries’, ‘Bali’, ‘India’, ‘Thailand’, and ‘Australia’.
NOTE: The selection you make here determines the name of the named range.
Let’s now set up the first drop-down list using Data Validation.
Now, we need to create a second list to display the Tours. This list will dynamically change depending on the Country selected.
Let us now create the second drop-down list which is dependent on the selection made in the first. To do this we are going to use Excels INDIRECT function.
The INDIRECT formula references the country listed in cell B14 and matches it to the named range of the same name to produce the correct drop-down list.
The previous example is a great way of creating a drop-down list using named ranges, data validation, and the INDIRECT functions, however, it does have some drawbacks. Named ranges are fixed so if you update your data, add new data in, etc. then you would need to edit the named range to include the new data. This can be quite time-consuming and inefficient.
In this example, I am going to show you how to create expandable dependent drop-down lists that will automatically update when new data is added.
Tables expand to accommodate new data BUT you cannot link directly to them from the Data Validation dialog box. So, we must also create a named range which we can link to.
First, we need to create a list that shows the countries.
NOTE: Selecting the entire row ensures that any new countries added will be included in the drop-down list.
Next, we are going to use the INDEX and MATCH functions along with Data Validation to create a list that expands when new items are added. You can practice the formula in a cell in the worksheet to ensure it works and then copies it into the Data Validation dialog box if you want to.
NOTE: Remember, you can press the F3 key to bring up a list of named ranges
These are just a few examples of how to create multiple data validation drop-down lists.