Dependent Drop Down List in Google Sheets: Usually a drop down list in Google Sheet is used to create a list of options where users can choose from. Drop down list is one of the most helpful functions in G-Suite since it helps us to save more time by avoiding spelling mistakes. However, in some instances, we feel like adding multiple drop down lists in Google Sheets. In this article, let’s understand how to create a dependent drop down list in Google Sheets for multiple rows in detail with important Google Sheet Tricks. Read on to find more.
Table of Contents
What is Multiple Dependent Drop Down List Google Sheets?
Firstly, let’s understand what a dependent drop-down list is with an example. Let’s say we have two columns, where Column A consists of a dataset of Fruits and column B with Vegetables. Now if we select Fruits in Column D, Column E should show only Fruits name in the drop-down list dataset. Similarly, if we select the Vegetable name in Column D, Column E in the spreadsheet should show only Vegetable names as shown below.
How to create a Named Ranges in Google Sheet?
In order to update the cell values based on the selection in the drop down list in the Google Spreadsheet, we should first create named ranges.
- How to Create a Drop Down List in Google Sheets: Add/Remove/Customize Drop Down Menu
- How to Quickly Merge Cells in Google Sheets: Merge & Unmerge Without Losing Data
- How to Wrap Text in Google Sheets: Expand Cell using Text Wrap
The steps to create named ranges in Google Sheet are explained below:
- Step 1: Select the cell where you would like to create a drop down list.
- Step 2: On the homepage, click on “Data” and select “Named Ranges“.
- Step 3: Now “Named Ranges” pane will open on the Google Sheets. Now click on “Add a range“
- Step 4: Mention the name for this “Named Ranges” and mention the cell range.
- Step 5: Click on “Done“. This will create first-named ranges.
Step 6: Now click on Add rule and follow the same steps mentioned above to create a Name range “Vegetable“. Like-wise you can create the number of ranges that you want to create.
Note: Only a single word should be used to name the range. When defining the named range, use underscores to unite the words if there are more than one. For instance, if the category is ‘Seasonal Fruits’ rather than ‘Fruits,’ label the designated range Seasonal_Fruits.
How to create Dependent Drop Down List in Google Sheets?
Now, it’s time to create drop-down list in the selected cell. Here we are selecting Cell D3 and follow the steps given below.
- Step 1: Click on “Data” and select “Data Validation” from drop down menu.
- Step 2: Select “List from a Range” under “Criteria” and enter the cell range.
- Step 3: Check the “Show Dropdown list in Cell” and click on the “Save” button.
- Step 4: Now you will see the drop down created in cell D3.
Step 5: Now, before we create the second drop-down, enter the formula =INDIRECT(D3) into an empty cell in the worksheet, or create a new worksheet if necessary. This would bring up a list of all the products in the category chosen in Drop Down 1. This is a dynamic dependant drop-down list, so if you change the drop-down in cell D3 from Fruits to Vegetables, the list will change to show the vegetables.
- Step 6: Now the next step is to create a dependent drop down list in E3. For this place, the cursor in the E3 cell and select “Data Validation” from “Data“.
- Step 7: Select the ‘Criteria‘ as ‘List from a range‘ in the Data Validation dialogue box, and then define the cells that contain the INDIRECT function result. It’s possible that various lists have varying numbers of items.
Step 8: Click on “Save”
Now you will see the dependent drop-down lists as shown below.