Keeping your data properly sorted and organized can be challenging, to say the least. However, computer programs such as Excel and Google Sheets make it easy not only to store your data but to sort and recover it using simple formulas. Moreover, formulas range in complexity and functionality, and one like a dependent drop-down list in Google Sheets can allow you to organize and access multiple types of data.
This post will look at how to create a dependent drop-down list in Google Sheets and provide practical examples you can follow.
Keep reading to learn more.
Table of Contents
What is a dependent drop-down list in Google Sheets?
A Google Sheets’ dependent drop-down list is a data validation function that allows you to create a cascading or hierarchical selection of options. It means the available options in one drop-down list (the dependent drop-down list) are governed by the choice made in another drop-down list (the parent drop-down list).
In other words, the options in the dependent drop-down list depend on the value chosen in the parent drop-down list. When you select a specific item in the parent drop-down, the options in the dependent drop-down change accordingly to reflect only the relevant choices associated with the selected item.
For instance, if you have a list of countries in the parent drop-down list and want to show cities as options in the dependent drop-down list based on a selected country. Now let’s say you choose the United States in the parent drop-down; the dependent drop-down will display a list of cities in the United States. If you change it to Canada, the dependent drop-down will show a list of cities in Canada, and so on.
Creating a dependent drop-down list in Google Sheets involves using the data validation feature and a combination of formulas like FILTER or VLOOKUP. It’s also a helpful way of organizing and streamlining data entry in your Google Sheets, especially when dealing with interconnected or hierarchical data. It makes the process more user-friendly and helps you select valid and relevant options based on previous selections.
Now let’s look at an example of creating a dependent drop-down list in Google Sheets.
Creating a Dependent Drop-down List in Google Sheets
For our example, we will look at a fruit vendor’s Google Sheets list and determine what type of fruits they have and how many are left depending on the fruit type from a separate list.
Here is what our Google Sheets look like.
To begin with, there are a few things you should know before we start.
You will be working with the indirect function and data validation. The Indirect function lets you enter an address, cell, or range and displays what you put in another selected cell.
On the other hand, data validation is the drop-down that allows you to put the drop-down in.
You should also note that we have two tabs in our example Google Sheets, quantity and sheet1. The quantity sheet contains the number of fruits that will be returned to the amount remaining column once you choose the type.
To create a drop-down on our list, you will:
- Pick a Cell
For this case, highlight cell A2 and go to your menu options and select Data, then Data Validation.
This will open a new menu option on the right-hand side of your screen.
- Select Criteria
On the criteria section, click the dropdown option and pick Dropdown (from a range).
- Select Data Range
Below the criteria box is another empty dialogue box with a grid pattern on the right side. Click on the grid pattern to open a dialogue box asking you to select the data range. Click on the dialogue box as if you want to type something, and then highlight the heading section of the fruits. This will be cells E1, F1, G1, and H1, and press enter.
If you look at your first cell, A2, you will note that dropdown options for your fruits have been added, as well as on the right-hand side of your screen.
Now we want to create another dropdown that returns the different types of fruit the vendor has for each type, which takes us to your next step.
- Define Named Ranges
To return the type of fruits each category of fruit has in cell A1, you need to use the named ranges function. So when you pick apples or any other fruit on the dropdown, this will allow the function to look for a named range called apples in your data set.
First, select a fruit from cell A1. In this instance, apple. Then go to the fruit list on the right side of the document and highlight the types of apples available under the apples category.
Then go to your menu section, select Data, and click Named Ranges.
A dialog box will appear on the right side of the screen, as seen below.
Replace the term NamedRange1 on the top dialog box with Apples and click Done. Now go back to the A1 cell, select the next fruit, and repeat the same process for all. Note that all the fruit names in the Named Range section should appear as they are on your document. Any typos or mismatches will result in an error message.
Your Google Sheets should look like this when done.
- Input Formula
Select a random cell on your sheet that does not interfere with your primary data set and input this formula:
Once you press enter, your results should be as follows.
- Data Validation
Now we want to populate the Type section of the document with the different types of variations each fruit has. Select cell B2, go to the Data section on the menu bar, and click Data Validation. A new menu section will appear on the right side of your document, as shown below.
Click on Add Rule and ensure the B2 cell is still highlighted, which should look like this.
Click on the grided box the arrow is pointing to open a new dialog box.
The next step is to use the results from entering the Indirect formula as your range for your data validation function. Highlight the three types of fruits and ensure to include extra cells below that to accommodate any fruit category with more than three types of fruits.
- Press Okay
Once you press okay, you should almost be done. Select a fruit in the A2 cell and use the new dropdown option in the B2 cell; it should display the different variations of that fruit the vendor has available, as seen below.
Moreover, if you change the type of fruit in the A2 cell, the results in the B2 cell should also change to reflect the types of fruits available for that particular one.
- Create Named Ranges for Quantity List
Open your second sheet on your Google Sheets document, which is the quantity list, and it should look like this.
Click on Data on the top menu section and select the Named Ranges option. It will open a new menu section on the right side of the screen with the previously named ranges. Highlight cell B1 and click on Add a Range.
A new dialogue box will appear, requiring you to name the range.
In the dialog box, the red arrow points to and says NamedRange1; delete that and replace it with the name of the fruit on the left side of the highlighted number in cell B1, which in this instance, is Fuji. Then press Done.
Keep the names consistent, as any misspelling will result in an error. Repeat the named ranges process for all the fruits before returning to sheet 1.
You should also note that you can’t have any spaces in your named ranges. You cannot start with a number or use true or false for the name. Therefore, any fruit with two names and a space between them will not work. Lady Finger is a two worded word in our example, but we’ve used an underscore to join the two.
So keep that in mind when working on your own documentation.
- Find the Amount Remaining
Now that you’re done with your data validation, the only thing left is to return the number of fruits the vendor has left to the Amount remaining column. You will use the Indirect function for this, which, as we’ve already seen, is very simple. Simply input this formula:
in the C2 cell.
Then press enter. The cell should automatically fill with the right amount of fruits left from the quantity list.
Moreover, if you change the fruit type in cells A2 and B2, the amount remaining for that particular fruit should automatically populate the C2 cell. If you also change the number of fruits remaining in the quantity sheet, the amount should also change in your results without making changes, which is convenient.
That is how to create a dependent drop-down list in Google Sheets and have it return a value from another corresponding sheet.
Running a business can be exciting and challenging at the same time. Moreover, keeping your business’s information well organized and easy to access while also running it can be almost impossible. However, dependent drop-down lists in Google Sheets allow you to manage, validate, and access your data conveniently.
Creating dependent drop-down lists in Google Sheets is also easy, as we’ve seen in this piece, and you should be up and running in no time. You can also use dependent drop-down lists to convey information from multiple sheets in one document, improving your efficiency and ability to gain insights into your business.
Therefore, use the information in this post to help you create your own drop-down list for your business to help you improve your operations. So what dependent drop-down lists in Google Sheets use cases do you foresee in your business? Let us know in the comments section below.
What is a dependent drop-down list in Google Sheets?
A dependent drop-down list in Google Sheets is a feature that enables the options in one drop-down list (dependent) to change dynamically based on the selection made in another drop-down list (parent). It allows you to choose relevant and valid options depending on your previous selection, making data organization and data entry more efficient.
Why should I use a dependent drop-down list in Google Sheets?
A dependent drop-down list in Google Sheets can help you maintain a structured and interconnected data entry system. It ensures you select appropriate options related to your previous choices, reducing errors and saving time during data input.
How do I set up the parent drop-down list?
To set up the parent drop-down list, enter the list of options in a column in your Google Sheets. Then, select the cell where you want the parent drop-down to appear and use the data validation feature to define the list of items as the range containing your options.
How do I create separate sheets for dependent options?
To create separate sheets for dependent options, create a new sheet for each option in the parent drop-down. Name each sheet with the corresponding option, and in each sheet, enter the list of options for the dependent drop-down in a column.
What formulas should I use for the dependent drop-down list?
You can use formulas like INDIRECT, FILTER, or VLOOKUP for the dependent drop-down list. These formulas help fetch the relevant options from the appropriate sheet based on the selection made in the parent drop-down.
Can I have multiple levels of dependent drop-down lists?
You can have multiple levels of dependent drop-down lists in Google Sheets. You can create a complex hierarchy of interconnected options using more than one set of dependent drop-downs, which provides a more comprehensive data selection.