Dependent Drop-down Chips in Google Sheets

You can take drop-downs to the next level by making them dynamic by choosing an option in the first drop-down menu to filter only relevant options in the second drop-down menu.

Following on from the Drop-down Chips tutorial, let’s explore how to make these drop-down lists dependent so that when you choose something from one drop-down, the options in the second drop-down are relevant instead of just displaying everything.

Everything is the same in creating the first drop-down list, so check out the post above to see how that’s done. Where the change comes is creating a dynamic filtered list for the subsequent drop-down lists.

Using this table of car manufacturers and their respective models, we want to create a filter so that when a manufacturer is chosen in the drop-down list, a dependent filter searches the table to retrieve models that relate to that selection.

Create a FILTER Function

We can use the FILTER function in Google Sheets to achieve this. We’ll start by typing out the FILTER function and the first argument, which is the range we want to filter; in this case, the models.

=FILTER(C2:C

Next up, we need to specify the condition to evaluate. We want to check column B (Manufacturers) and check if this is equal to our drop-down selection.

This works based on a TRUE or FALSE condition, so if the value in cell F2 is equal to what is found in the B column, then the values from the C column will be returned.

=FILTER(C2:C,B2:B

We’ll add an equals sign followed by the cell with the drop-down (F2) to complete the function.

You will, of course, need to add a final parentheses to close the formula.

=FILTER(C2:C,B2:B=F2)

Now, you can see the results in column D are only showing models from column C where the Manufacturer is equal to ‘Tesla’ because that’s what we have selected in the drop-down list in cell F2.

Add Second Dependent Drop-Down List

Finally, we can now add our second drop-down chips to the sheet. Follow the steps you used before for creating a drop-down, but when you choose the range for this second drop-down list, you will use the newly created dependent filter we created in column D.

It’s always best to make the range open-ended by dropping the digits; this ensures that if the filter list grows in length, then everything will be included in the drop-down.

Dropdown!D2:D

When you choose a Manufacturer in the first drop-down, the second drop-down list will only display models that relate to that Manufacturer.

Multiple Dependent Drop-Down Lists

Handling Errors

There is a slight issue if you change the first drop-down selection. You’ll notice that an error triangle appears because the second drop-down list cannot find the previously chosen option when the first drop-down is changed.

Google Apps Script - Clear Cell Contents

Thankfully, there is a solution using Google Apps Script. Check out this video here to see how that’s done. It’s nice and easy; the video shows you all the steps you need to set this up, and I’ll add the script below, which you can copy and paste.

You’ll need to change four things in this script to make it work on your sheet.

1. e.getRow()== 2

2. e.getColumn()== 6

3. ss.getName()=='Dropdown'

4. ss.getRange('G2')

You will need to replace the row number with the row number your drop-down list is in.

The column number A = 1, B = 2, etc. The name of your sheet (The tab name). Finally, the G2 cell should be changed to the cell where your second drop-down is positioned in your sheet.

Script

function onEdit(e) {

var ss = SpreadsheetApp.getActiveSheet();

var e = ss.getActiveCell();

if (e.getRow()== 2 && e.getColumn()== 6 && ss.getName()=='Dropdown') {

ss.getRange('G2').clearContent();

}

}

Impress your colleagues and friends with dynamic drop-down chips and your first taster of Google Apps Script to automate your sheets.