How to create dynamic dependent dropdown Lists

Raise your hand if you’ve had to create a tracker for something. Even with all the sophisticated tools available, there will inevitably be situations in our personal and professional lives where keeping track of things in a well designed spreadsheet can be really useful. The whole point of creating a tracker is to keep data organized. So here’s a tip that can help you avoid a common challenge.

Let's talk about dropdown lists. They’re essential to keep data clean, and Excel and Google Sheets have made it easier than ever to create them. With dropdown lists, you can avoid typos and get consistent information that can be used to aggregate information into summary tables and pretty charts (more on that in future posts). So what's the challenge?

The challenge?

You’ve created a beautiful spreadsheet for keeping track of things with a bunch of dropdown lists. Hooray for consistency and avoiding typos! Over time, you’ll need to update your dropdown lists and will start to notice that there are relationships between different dropdown lists that are getting ignored. Uh oh. If I select a value in one list, I need it to narrow down the available options on another list. Personally, this could be expense categories and sub-categories for budgets. Professionally, a common one is organization hierarchy. I only want to pick from a subset of teams based on what gets selected for the department, not a gigantic list of all teams. Without some logic, you end up with structured data from the dropdowns, but the data doesn’t make sense. For example, you end up with a department of Engineering and a team of Accounting.

Create dynamic dependent dropdown lists

First, thank you for allowing me to nerd out on this one. Let’s use the organization hierarchy example and up-level that tracker of yours, and use Google Sheets to walk through how to solve this. It’s not as simple as clicking a button, so here’s how you do it.

First, here’s an example tracking doc so that you can see the solution and practice. You can make a copy to follow along step-by-step.

Step 1: Create a new sheet called ‘mapping’ to get your data organized so that all of your departments and teams are listed in their hierarchy.

The most common way I’ve seen this is with one column with Department names and another column with the related Team names. For example:

• A1: Department B1: Team
• A2: Sales B2: Enterprise Sales
• A3: Sales B3: SMB Sales
• A4: Sales B4: Account Management

Step 2: On the sheet containing your ‘tracker’ data, create a dropdown with the unique list of Departments.

• Go to the tracker sheet, find the Department column, and click into the cell where you want the dropdown to be (B2).
• Right-click and select Dropdown.
• In Criteria, select Dropdown (from a range) and then select the range where only your department names are listed in your mapping sheet. Tip: select the entire column so if you add new departments they will automatically get added to the list. Use the $ in front of the column (A) and start the range on row (2) so that the column title won’t appear in the dropdown. In this example, I entered =’mapping'!$A$2:$A.
• Now, copy and paste your new dropdown list down the Department column so it’s available on all rows.

Step 3: Create a new sheet called ‘teamlist’.

This sheet will serve as the output of values based on the Department selections you’ve made in the tracker, and will be referenced to pull dependent values in your Team dropdown list.

• Add a column heading ‘Team List’ in cell A1
• We’ll be using the following formula in cell A2

=ifna(transpose(filter('mapping'!B:B,'mapping'!A:A=' tracker'!B2)),"")        

At a high-level, this is what’s happening in the formula:

• You select a department.
• The formula finds the selected department in the range of departments in your mapping.
• Then, it filters the list of teams based on the specific department you’ve selected.
• It rearranges the teams from a column to a row.
• If no department has been selected for a row on your tracker, instead of showing an error, it shows nothing.

Specifically, here's a breakdown of what each component is doing:

• IFNA - if you haven’t selected a department in your tracker data, this will output blank instead of an error
• TRANSPOSE - this will output all relevant team names horizontally so each row displays teams based on the department that is selected on your tracker

• FILTER - this formula looks for a match of department name on your tracker to the mapping and returns the related team names. Let's breakdown each component:

'mapping'!B:B - this is the range of values that will be filtered, team names.

'mapping'!A:A=' tracker'!B2 - looks at the value you’ve selected for department in your tracker (sheet tracker where column B is Department) and matches against the list of all departments. Where it finds a match it will filter down to just the relevant list of team names.

Copy and paste the formula down on all rows for your Team List column.

Step 4: On the tracker sheet, create a dropdown for Teams.??

You’ve done the hard part, now the last step is to create a dropdown for Teams on your tracker. Let’s create the dependent list of teams using the ‘teamlist’ you just created.?

• Go to the tracker sheet, find the Team column, and click into the cell where you want the dropdown to be (C2).
• Right-click and select Dropdown.
• In Criteria, select Dropdown (from a range) and then select the range for the first row of team values on the teamlist sheet. A tip: select the entire row so if you add new teams to the department they will automatically get added to the list.?
• Be sure that 1) the $ is in front of the column reference ($A and $Z) so that it's locked in, and 2) there is not a $ in front of the row (2). This will ensure that when you copy and paste the formula that the columns don't change but that the row increments. In this example, I entered =’teamlist’!$A2:$Z2
• Now, copy and paste your new Team dropdown list so it’s on all rows in the tracker.

Let's test it out. Go to your tracker and select a couple Departments in the dropdown. Now, select Teams using your new dropdown. You should see only the relevant teams for the department you’ve selected for each row.

Things to keep in mind:

• You can add or change departments and teams at any time
• If you want to hide the mapping and teamlist sheets just right-click on them and select “hide sheet”.
• New rows need to be added to the bottom of the tracker to avoid breaking the formulas. This is an unfortunate limitation of Google Sheets that doesn’t update the row reference of the Team dropdown validation if you insert rows. An option to avoid this limitation is to have your Teams dropdown include all teams. Then, create a validation column to include logic to check for valid pairs of departments and teams.

Now, sit back and enjoy your new dynamic dependent dropdown lists! Learn more about Growth by Design Talent by visiting our website or subscribing to our newsletter to get talent insights and recruiting leadership tips.