If you haven’t had a chance to read about Dependent Dropdown lists in Google Sheets, take a moment to click the image below to read (or watch) a complete walkthrough.
Setting Up the Supporting Data
We have a spreadsheet where the first dropdown list will be created in cell A3 while the dependent (second) dropdown list will be created in cell B3.
The lists for the two dropdowns are located on a sheet named “Master” where the first list points to the heading row of the table (cells A3:C3) and the second list points to the remaining items in the table (cells A4:C20). A few extra rows were selected to accommodate additions to the table.
Featured Course
Google Sheets – The Comprehensive Masterclass
Creating the First Dropdown List
To create the dropdown for Division, perform the following steps:
- Select cell A3.
- Select Data -> Data Validation.
- In the Data Validation dialog box, select “List from a range” (now called “Dropdown (from a range)”) in the Criteria section and define the range as Master!A3:C3. 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 dropdown button located in cell A3 to reveal the following list of Divisions.
When we copy cell A3 and paste it to range A4:A12, we see that the Data Validation logic is replicated across all pasted cells.
Google Sheets Update
Please note that Google Sheets has updated the Data Validation experience since the video was recorded. Instead of a pop-up window, you get a pane on the right-hand side. The default style of a dropdown is now a “chip”, but you can change it to the standard “arrow”, like you see in the video.
And crucially, the dollar signs that apply the absolute reference to dropdown ranges are no longer implied. You see them and can edit them like you would in Excel.
Setting Up the Second (Dependent) Dropdown List
Now we create dropdown lists that are reactive to the first dropdown lists. These must work for each row independently of the adjacent rows.
Remember 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.
Creating an Index List
Returning to the “Master” sheet, we will create a list of numbers from 1 to 10 in cells E4:E13. Each of these numbers corresponds to a “first” dropdown on the input sheet.
Creating the App Lookup List
To create a list of Apps that correspond to the user’s selection in the first dropdown (cell A3 – input sheet), select cell F4, and enter the following formula.
=INDEX($A$4:$C$20, , MATCH('Dep. List'!A3, $A$3:$C$3, 0) )
The result is a list of Apps from the selected Division.
To break the formula down into its pieces:
- The INDEX function uses the first argument to select the array of cells $A$4:$C$20.
- We want ALL ROWS to be returned, so we omit the second argument in the INDEX function, hence the 2 commas with nothing between them.
- The third argument uses a MATCH function to locate the item listed in cell A3 of the input sheet within the list of Department in cells $A$3:$C$3. This match must be an exact match, so a 0 is used to set this behavior in the MATCH.
Transposing the Results
Because we want our results to be listed horizontally (across the row) instead of vertically (down the column), we will nest the entire INDEX/MATCH formula within a TRANSPOSE function.
=TRANSPOSE(INDEX($A$4:$C$20, , MATCH('Dep. List'!A3, $A$3:$C$3, 0) ) )
Filling Down for the Remaining Lists
If we take the formula created in cell F4 and fill it down to cell F13, we are presented with a panic moment.
Not to worry. This is only because we have not selected any Divisions from the remaining dropdowns. These errors will disappear once we occupy the remaining division dropdowns.
But who wants to look at errors that aren’t real errors?
To suppress the #N/A error messages, we will nest the entire TRANSPOSE/INDEX/MATCH formula within an IFNA() function.
=IFNA(TRANSPOSE(INDEX($A$4:$C$20, , MATCH('Dep. List'!A3, $A$3:$C$3, 0) ) ) )If we replace the original TRANSPOSE formulas with the updated formula, we see the following results.
Copying the Dependent Dropdown to More Rows
We return to the input sheet to set up the dependent dropdown list for the Apps.
To create the dropdown for Division, perform the following steps:
- Select cell B3.
- Select Data -> Data Validation.
- In the Data Validation dialog box, select “List from a range” (“Dropdown (from a range)”) in the Criteria section and define the range as Master!$F$4:$Z$4. We also wish to disallow any custom data from being entered into the cell, so we activate the “Reject input” option. A few extra columns were selected to accommodate additions to the table.
The result is a list of Apps for the selected Division.
If we copy the B3 cell and paste it across the remaining Column B cells, we don’t get what we were expecting from the dependent dropdowns.
Every dependent dropdown has the list for whatever Division is selected in cell A3. This is because all dependent dropdown lists are looking at range F4:Z4 for their list of Apps.
To solve this, you must edit the criteria range applied to cell B3 before you copy the data validation down.
Since we’re copying the cells across rows rather than columns, all we need to do is remove the dollar sign from the row number. So, instead of =Master!$F$4:$Z$4 we should have =Master!$F4:$Z4.
Now, when you copy the data validation cell down column B, the row references to the data preparation range shift as well and match the selection in the department dropdown.
Practice File
Feel free to get your own copy of the file HERE.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
