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

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.

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.

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.

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.

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.

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

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.

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

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.

Gallery of Dependent Drop-Down Lists

Dependent Drop-Down Lists Gallery

Dependent Drop-Down Lists Best Practices

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.