5 Ways To Create A Dependent Drop-Down In Google Sheets

Intro

Master dynamic data filtering in Google Sheets with dependent drop-downs. Learn 5 simple methods to create cascading drop-down lists that auto-update based on user selections. Discover how to use Google Sheets formulas, functions, and data validation to streamline data entry and reduce errors. Get step-by-step tutorials and examples inside.

In Google Sheets, creating dependent drop-down lists is a powerful feature that can help you to make data entry easier, faster, and more accurate. A dependent drop-down list is a list that changes based on the selection made in another list. For instance, if you have a list of countries and you want to create a list of cities that changes based on the country selected, you can achieve this by creating a dependent drop-down list. Here are five ways to create a dependent drop-down in Google Sheets.

What are Dependent Drop-Down Lists?

Dependent drop-down lists are a type of data validation in Google Sheets that allows you to create a list of options that changes based on the selection made in another list. This feature is useful when you have a dataset that has multiple categories and subcategories, and you want to make it easier for users to select the correct options.

Dependent Drop-Down Lists

Method 1: Using Data Validation

One of the easiest ways to create a dependent drop-down list in Google Sheets is by using data validation. Here's how:

  • Select the cell where you want to create the dependent drop-down list.
  • Go to the "Data" menu and select "Data validation".
  • In the data validation window, select "List of items" from the dropdown menu.
  • In the "Criteria" field, enter the range of cells that contains the list of options.
  • Click on the "Save" button.

Now, let's say you have a list of countries in cells A1:A10, and you want to create a list of cities that changes based on the country selected. You can create a dependent drop-down list in cell B1 by following these steps:

  • Select cell B1.
  • Go to the "Data" menu and select "Data validation".
  • In the data validation window, select "List of items" from the dropdown menu.
  • In the "Criteria" field, enter the formula =INDIRECT(A1), assuming that the list of cities for each country is in a separate column.
  • Click on the "Save" button.
Data Validation

Method 2: Using Named Ranges

Another way to create a dependent drop-down list in Google Sheets is by using named ranges. Here's how:

  • Select the range of cells that contains the list of options.
  • Go to the "Data" menu and select "Named ranges".
  • In the named range window, enter a name for the range, such as "Countries".
  • Click on the "Done" button.

Now, let's say you have a list of countries in cells A1:A10, and you want to create a list of cities that changes based on the country selected. You can create a dependent drop-down list in cell B1 by following these steps:

  • Select cell B1.
  • Go to the "Data" menu and select "Data validation".
  • In the data validation window, select "List of items" from the dropdown menu.
  • In the "Criteria" field, enter the formula =Countries, assuming that the list of cities for each country is in a separate column.
  • Click on the "Save" button.
Named Ranges

Method 3: Using Index-Match Function

You can also create a dependent drop-down list in Google Sheets using the index-match function. Here's how:

  • Select the cell where you want to create the dependent drop-down list.
  • Enter the formula =INDEX(C:C,MATCH(A1,B:B,0)), assuming that the list of cities for each country is in column C, and the list of countries is in column B.
  • Press the "Enter" key.

Now, let's say you have a list of countries in cells A1:A10, and you want to create a list of cities that changes based on the country selected. You can create a dependent drop-down list in cell B1 by following these steps:

  • Select cell B1.
  • Enter the formula =INDEX(C:C,MATCH(A1,B:B,0)).
  • Press the "Enter" key.
Index-Match Function

Method 4: Using VLOOKUP Function

You can also create a dependent drop-down list in Google Sheets using the VLOOKUP function. Here's how:

  • Select the cell where you want to create the dependent drop-down list.
  • Enter the formula =VLOOKUP(A1,B:C,2,FALSE), assuming that the list of cities for each country is in column C, and the list of countries is in column B.
  • Press the "Enter" key.

Now, let's say you have a list of countries in cells A1:A10, and you want to create a list of cities that changes based on the country selected. You can create a dependent drop-down list in cell B1 by following these steps:

  • Select cell B1.
  • Enter the formula =VLOOKUP(A1,B:C,2,FALSE).
  • Press the "Enter" key.
VLOOKUP Function

Method 5: Using Google Apps Script

You can also create a dependent drop-down list in Google Sheets using Google Apps Script. Here's how:

  • Open the Google Apps Script editor by going to the "Tools" menu and selecting "Script editor".
  • In the script editor, enter the following code:
function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var value = e.value;
  
  if (range.getColumn() == 1 && range.getRow() > 1) {
    var country = value;
    var cities = [];
    
    // assuming that the list of cities for each country is in a separate sheet
    var citySheet = e.source.getSheetByName('Cities');
    var cityRange = citySheet.getRange(1, 1, citySheet.getLastRow(), 1);
    var cityValues = cityRange.getValues();
    
    for (var i = 0; i < cityValues.length; i++) {
      if (cityValues[i][0] == country) {
        cities.push(cityValues[i][1]);
      }
    }
    
    var dropDownRange = sheet.getRange(range.getRow(), 2);
    var dropDown = sheet.getValidation();
    dropDown.requireValueInList(cities);
    dropDownRange.setDataValidation(dropDown);
  }
}
  • Save the script by clicking on the floppy disk icon or pressing "Ctrl+S".
  • Go back to your Google Sheet and select the cell where you want to create the dependent drop-down list.
  • Enter a value in the cell, and the dependent drop-down list will appear.
Google Apps Script

Gallery of Dependent Drop-Down Lists

Conclusion

Creating dependent drop-down lists in Google Sheets is a powerful feature that can help you to make data entry easier, faster, and more accurate. In this article, we have discussed five ways to create a dependent drop-down list in Google Sheets, including using data validation, named ranges, index-match function, VLOOKUP function, and Google Apps Script. We have also provided examples and use cases to illustrate the benefits and best practices of using dependent drop-down lists.

Jonny Richards

Love Minecraft, my world is there. At VALPO, you can save as a template and then reuse that template wherever you want.