Dynamic Dropdown Menus – Google Sheets

Learn how to create dropdown menus that will dynamically populate based on another dropdown menu. Copy our practice sheet to see this concept in action.

Copy Practice Sheet

What Is A Dynamic Dropdown Menu

The goal of a dynamic dropdown menu is to show only relevant information in subsequent dropdowns. Also, known as dependent dropdowns, because the contents of a menu are dependent upon another dropdown’s selection. There are 2 different techniques to creating a dynamic dropdown menu.

  1. Filter Method
  2. Named Ranges Method
  3. Index Match Method

In our practice sheet we create a dynamic dropdown to only show cities that are within a selected state and a dropdown menu for the CONVERT function. We want to create a dynamic dropdown that will only show relevant variables to the dependent dropdown.

Practice Sheet Scenario

We have created a formula that will find a cities average temperature for any selected month. To help with this user experience we only want to show cities that are in the selected state.

Filter Method

The filter method takes advantage of the FILTER function. It works great for generating lists with corresponding variables. This means we have a list of data that we can easily filter by a cell in the corresponding column or row.

In our practice sheet you will find a spreadsheet of cities and the state they belong to under the “Data Validation FILTER” tab. The goal of our dynamic dropdown is when a user selects a state the cities dropdown menu will on show cities within that state. For instance, if we select Florida from the state dropdown we will want to show; Jacksonville, Miami, Palm Springs, and Tampa in the cities dropdown menu.

The first step is to set up a data validation for the city select dropdown. We will use “List from a range” criteria. This will allow our data validation to display what ever is the selected range. In our practice sheet you can see we set our range to 'Data Validation FILTER'!D3:D54. This ensures we will always pull all data our filter function extracts.

Next we need to write our FILTER formula. In 'Data Validation FILTER'!D3 we can add the code as seen below.

=FILTER(B:B,A:A='Dynamic Menu FILTER'!A2)

Formula Explained

Our formula states that we would like to filter the data in our cities column. The A:A='Dynamic Menu FILTER'!A2 is the condition to filter our cities column by. We are stating that we would like to filter column B if column A contains our selected state from our dropdown in the “Dynamic Menu” tab.

The FILTER function can be expanding by adding additional conditions, this is the simplest way to create a dynamic dropdown. But there are times that the Filter Method is limited if your data you wish to filter doesn’t have corresponding cells.

Practice Sheet Scenario

In our practice sheet we need a dynamic dropdown menu that will convert values from one unit to another, but for the units to be converted they have to be in the same category. For instance, we cannot convert Fahrenheit to Years, because Fahrenheit is a in the Temperature category and Years is in the Time category.

When the category of conversion is selected, we only want to show start units and end units that pertain to that category.

For the start and end unit we will create a dynamic menu using the named ranges method. For the CONVERT function output we will use the Index Match Method. Both methods will work, it just depends on your preference.

Named Ranges Method

The Named Ranges Method works by naming a specific range and then using the INDIRECT function to reference the named range. Remember the goal is for a user to be able to select a category (Temperature, Weight, Distance, etc.) and only show units within a category (Time: Second, Minute, Hour, Day, Year) to convert a value from one unit to another.

  1. First we need to use data validation to create the category dropdown list. We will use the “List from a range” criteria and reference column A in the “Data Validation” tab. We now have a dropdown menu of the 13 different conversion categories Google Sheets recognize in their CONVERT function.
  2. We want the starting and ending unit to correspond to the selected category. In the variables tab you can see all acceptable variables the Google Sheets CONVERT function recognizes and in row 1 you can see each category the units belong to. We will create named ranges by selecting the appropriate cells and select Data Named ranges in the menu. We will name each of the different categories the exact same name we used in the data validation in step 1. For example, Variables!A1:A12 range will need to be named Weight, because Weight is the exact spelling of an available category we created in step 1. Review the named categories.
  3. Now we can utilize the INDIRECT function to pull the named range from the referenced cell. By setting the formula below in cell B1 of the “Data Validation” tab the corresponding named range will appear down column B.
  4. The last step is to set our data validation for the starting and ending units in C2 and D2 to create a dropdown from a “List from a range” 'Data Validation'!B2:B.
=INDIRECT('Dynamic Menu'!B2)

When we change the category now, the stating unit options change to match the corresponding category.

Index Match Method

The Index Match Method works in a similar way as the INDIRECT method, but will not require you to make named ranges. The advantage to not needing named ranges is it makes it simple to easily pull specific columns. Since, we will need to extract the variables that correspond to each category (Fahrenheit is “F” in the CONVERT function). Rather than make an additional 13 named ranges for the unit variables we will use the Index Match Method.

In cell AD in our “Variables” tab we will place this function.

=INDEX(Variables!A1:Z26,,MATCH('Dynamic Menu'!B2,Variables!1:1,0)+1)

Formula Explained

We start by indexing all of the variables. We then don’t set a specific row in the INDEX function so we will pull an entire column. To extract the correct column location we use the MATCH function. The match function allows us to match the selected category in 'All Available Conversions'!B2. This is the data validation we setup in step one in the Named Range instructions. We are searching for an exact match for the category (e.g. Weight) in row 1. The 0 means it needs to be an exact match. The reason we are adding a plus one is we want to extract the corresponding variables and not the starting or ending unit name. If we removed the +1 we would produce the same list we created using the Named Range method.