How To Create a Dependent Drop-Down List in Google Sheets
A dependent drop-down list is a type of data validation that changes based on the selection of another drop-down list.
This can be a great way to make your data more interactive.
For example, let’s say you have a list of countries in one drop-down list and a list of cities in another.
You want the city drop-down list to change based on the country drop-down list selection.
This guide will show you how to create a dependent drop-down list in Google Sheets.
How To Set Up a Dynamic Drop-Down List in Google Sheets
Here’s how to set up a dynamic drop-down list in Google Sheets.
First, set up the options for the first drop-down list in a single row.
These options will also act as the header for each column.
Next, input the choices you want to display in the second drop-down menu.
In this example, we’ve set up a list of cities for each continent option.
We’ll now create a named range for each sub-group. Select the first group of options under the first header.
Click on the Named ranges option under the Data menu.
Provide the header as the name of the range and click Done to proceed.
Create named ranges for each sub-group.
Next, select an empty cell to place the first drop-down menu.
Click on the Data validation option under the Data menu.
In the Data validation rules panel, select Dropdown (from a range) as the criteria option. Provide the range that includes the options you want for the first drop-down list.
Next, we’ll populate a new range with the appropriate sub-group using the INDIRECT function.
The INDIRECT function allows the user to return a named range.
Create a new drop-down menu with the INDIRECT output as options.
You should now have a dependent drop-down list that shows the appropriate options based on the first drop-down list.
If we change the option in the first drop-down list, the second drop-down list will update with new options.
This guide should be everything you need to create a dependent dropdown list in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.