The Objective
Our objective is to create two dropdown lists. The first list will present a list of Divisions while the second dropdown will present a list of Apps.
We only wish to see items from the Apps list that are related to the selected Division. This makes the second dropdown shorter and easier to navigate.
We will present two different methods for achieving this behavior. The first method will utilize a table as its list source while the second method will utilize a report as its source list.
For simplicity’s sake, the examples below will have the dropdown lists and the source tables on the same sheet. In the Real World, the source tables would be stored on a separate sheet and likely hidden from the user.
Method 1 – Dropdowns from a Table
Our data source is a list of Apps preceded by their associated Division.
Creating the Dropdown for Division
We will create a dropdown list in cell A4 that presents a unique list of Divisions.
One of the great things about Google Sheets is that the Data Validation tool will automatically remove the duplicates from a selected range. This renders a unique list of items.
To create the dropdown for Division, perform the following steps:
- Select cell A4.
- Select Data -> Data Validation.
- In the Data Validation dialog box, select “List from a range” in the Criteria section and define the range as E5:E. We also wish to disallow any custom data from being entered into the cell, so we activate the “Reject input” option. (By leaving the range open at the end, the list will recognize additions to column E.)
We can click the dropdown button located in cell A4 to reveal the following list of Divisions.
Creating the Dependent Dropdown for Apps
This is the tricky part; creating a dropdown list that is reactive to the first dropdown list.
We only wish to display Apps that are associated with the selected Division from the first dropdown list.
Creating the Data Preparation Area
As Google Sheets will not allow us to create a formula directly in the cell where the dropdown is to exist, we will write the formula in a “helper column” and reference the results with Data Validation.
The formula we will write to create a list of associated Apps based on the selected Division utilizes the FILTER function.
If you are unfamiliar with the FILTER function in Google Sheets, click the following link to read and view all about this amazing Sheets function.
Google Sheets – FILTER Function
To create the “helper function”, select cell H5 and enter the formula:
=FILTER(F5:F, E5:E = A4)We are presented with a list of associated Apps for the selected Division.
BONUS: If you would like to have the results of the “helper function” displayed in alphabetical order, wrap the FILTER function within a SORT function, like the following formula shows.
=SORT(FILTER(F5:F, E5:E = A4) )
Creating the Second Dropdown List
Creating the second dropdown involves the following steps:
- Select cell B4.
- Select Data -> Data Validation.
- In the Data Validation dialog box, select “List from a range” in the Criteria section and define the range as H5:H. We also wish to disallow any custom data from being entered into the cell, so we activate the “Reject input” option.
We can click the second dropdown button located in cell B4 to reveal the following list of associated Apps.
Testing the Dropdown Dependency
If a different Division is selected from cell A4, a new list of Apps is presented by the Apps dropdown in cell B4.
GOOGLE SHEETS – THE COMPREHENSIVE MASTER CLASS
Method 2 – Dropdowns from a Report
Our data source is a list of Divisions as column headings with their associated Apps listed below.
Creating the Dropdown for Division
We will create a dropdown list in cell A6 that presents a unique list of Divisions.
To create the dropdown for Division, perform the following steps:
- Select cell A64.
- Select Data -> Data Validation.
- In the Data Validation dialog box, select “List from a range” in the Criteria section and define the range as J4:L4. We also wish to disallow any custom data from being entered into the cell, so we activate the “Reject input” option.
Creating the Dependent Dropdown for Apps
We only wish to display Apps that are associated with the selected Division from the first dropdown list.
Creating the Data Preparation Area
Just as with the previous example, Google Sheets will not allow us to create a formula directly in the cell where the dropdown is to exist, so we will write the formula in a “helper column” and reference the results with Data Validation.
The formula we will write to create a list of associated Apps based on the selected Division utilizes the INDEX/MATCH functions.
If you are unfamiliar with using the INDEX/MATCH functions, click the following link to read and view all about these fantastic functions.
<<<<<< INSERT LINK HERE >>>>>>>
To create the “helper function”, select cell N5 and enter the formula:
=INDEX(J5:L30, , MATCH(A6, J4:L4, 0) )We are presented with a list of associated Apps for the selected Division.
To explain in plain terms what this formula does…
- The MATCH function locates the selected Division from cell A6 within cell J4:L4 (the 0 instructs MATCH to find it exactly).
- The discovered column number (1, 2, or 3) is returned to INDEX that returns all rows from the same numbered column in range J5:L30.
NOTE: 2 commas separate the INDEX function from the MATCH function. The argument that would rest between these commas denotes the number of rows to return from the defined column. As this has been left empty, the behavior of INDEX is to return all rows.
As seen in the first solution, if we wish to have the results displayed in alphabetical order, nest the INDEX/MATCH function within a SORT function.
=SORT(INDEX(J5:L30, , MATCH(A6, J4:L4, 0) ) )
Creating the Second Dropdown List
Creating the second dropdown involves the following steps:
- Select cell B6.
- Select Data -> Data Validation.
- In the Data Validation dialog box, select “List from a range” in the Criteria section and define the range as N5:N. We also wish to disallow any custom data from being entered into the cell, so we activate the “Reject input” option.
We can click the second dropdown button located in cell B4 to reveal the following list of associated Apps.
Testing the Dropdown Dependency
If a different Division is selected from cell A6, a new list of Apps is presented by the Apps dropdown in cell B6.
*** BEWARE ***
Be mindful that if an App is selected from the second dropdown, then the user selects a different Division, the displayed app will not correspond to the newly selected Division. The App cell does not automatically reset (i.e. clear) itself when a conflicting Division is selected.
To imbue the cell with this “auto-clearing” ability you will need to employ App Scripts. This is a topic covered in my Google Sheets Master Class. Check it out below.
GOOGLE SHEETS – THE COMPREHENSIVE MASTER CLASS
Click the image to the right to read about creating MULTIPLE dependent dropdown lists in Google Sheets.
Link to Practice File
Feel free to get your own copy of the file HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
