Create Multiple Dependent Drop Down List in Google Sheets

Imagine you have a dataset containing several categories of information. Each category contains various unique information. Now you need to create multiple dropdown lists for your reader so that they can choose a particular set of data followed by their required information from that dataset. How can you do that? Well, you can easily create multiple dependent drop down list in Google Sheets to get the required information. Follow the article to see the step-by-step procedure to do that in Google Sheets.

Table of Contents

A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice by yourself.

Multiple Dependent Drop Down List

What Is Dependent Drop Down List in Google Sheets?

In Google Sheets, a dropdown list is very useful to select a particular value from a list of options. This works only for a single list of values or options. Suppose you are working with 2 dropdown lists. The 2nd dropdown list depends on the option selected in the 1st 1st dropdown list. Then the 2nd one is a dependent dropdown list. There can be a 3rd drop down list depending on the 2nd one and so on.

For example, assume the 1st dropdown list contains “Electronic device” and “Wooden Device”. If you select Electronic device, then the 2nd dropdown list will contain TV, Freeze, Washing Machine, etc. If you select Wooden Device, then the 2nd dropdown list will contain Table, Chair, Box, etc. After that, if you select TV in the 2nd drop down list then the 3rd drop down list will contain LG, Sony, etc. If you select Table then the 3rd drop down list will be Steelcase, Ashley Furniture industries, etc. You can create multiple dependent dropdown lists in Google Sheets to choose the required information that meets all the criteria.

Step-by-Step Procedure to Create Multiple Dependent Drop Down List in Google Sheets

Consider the following image showing three dropdown lists. The dropdown list for Country is dependent on the selection in the dropdown list for Continent. Similarly, the dropdown list for City is dependent on the selection in the dropdown list for Country. Follow the step-by-step procedure below to create multiple dependent dropdown list in Google Sheets like this.

Step 1: Prepare Dataset

First, prepare a dataset that contains the names of Continents, Countries, and cities. Here the 1st dropdown list for continents will be the independent drop-down list. The 2nd and 3rd dependent dropdown lists will contain the names of countries and cities respectively.

Step 2: Create Named Ranges

Now you need to create Named Ranges for continents, countries, and cities. Select Data >> Named Ranges from the menu to do that.

Then the Named ranges pane will pop up. Select Add a range in there.
Next type Continent in the name field and enter B5:B6 into the range filed below. You can also select the range by clicking on the icon there. Here range B5:B6 contains the list of continents.

After that, create more Named Ranges for Asia, America, Nepal, Japan, Canada, and Mexico in the same way. Then you will see the following list of named ranges.

Step 3: Apply Data Validation

Next create three columns named Continent, Country, and City for the dropdown lists.

After that select cell G5 and go to Data >> Data Validation from the menu bar.

Next, the Data validation window will pop up as below.

Now, select range B5:B6 into Enter a range or formula box using the criteria List from a range.
After that, select the checkbox for Show drop down from the list, the radio button for Reject input, and then click Save to apply the Data validation.

As a result, the 1st dropdown list in cell G5 will be as follows.

Similar Readings

Step 4: Prepare Data for 1st Dependent Drop Down List

Now create a Data Preparation table for the dependent dropdown lists.

Next, select cell B15 and insert the following formula.

=INDIRECT(G5)

Step 5: Insert 1st Dependent Drop Down List

Next, select cell H5 to create a dropdown list as before using the range B15:B16.

Then the 1st dependent dropdown list will look as follows.

Step 6: Prepare Data for 2nd Dependent Drop Down List

Now, select cell C15 and enter the following formula to get the contents of the final dropdown list.

=INDIRECT(H5)

Step 7: Create 2nd Dependent Drop Down List

Next, select cell I5 to create the 2nd dependent dropdown list as before using the range C15:C17.

Finally, the 2nd dropdown list will look as follows.

Read More: How to Edit Drop-Down List in Google Sheets

Things to Remember

You cannot use the INDIRECT function in the formula box for data validation criteria.
Always lock the cell ranges in the Data validation window to avoid data missing or data mismatch.

Conclusion

In this article, we have shown how to create multiple dependent drop down list in Google Sheets. Now you can implement the steps on your own datasets. If you have any queries or suggestions, please let us know in the comment section. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.