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.


A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice by yourself.


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.

multiple dependent drop down list google sheets


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.

Prepare Dataset in google sheets


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.

Create Named Ranges un google sheets

  • 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.

Read More: Create Drop Down List for Multiple Selection in Google Sheets


Step 3: Apply Data Validation

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

Apply Data Validation in google sheets

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

multiple dependent drop down list google sheets

  • 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.

multiple dependent drop down list google sheets

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

Read More: How to Edit Data Validation in Google Sheets (With Easy Steps)


Similar Readings


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

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

Prepare Data for 1st Dependent Drop Down List

  • 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.

Insert 1st Dependent Drop Down List

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

Read More: How to Insert a Drop-Down List in Google Sheets (2 Easy Ways)


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)

Prepare Data for 2nd Dependent Drop Down List


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.

Create 2nd Dependent Drop Down List

  • 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.


Related Articles

Afrina

Afrina

This is Afrina Nafisa. Currently working as a Technical Writer at OFFICEWHEEL.COM. I have done my graduation from Ahsanullah University of Science and Technology. As I am eager to learn more and more currently my motive is to make myself better every day with my work so that I can make better content for all the readers.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo