A multiple dependent drop-down list in Google Sheets enables interconnected drop-down menus where selections in one list determine options in the next. This functionality is useful for organizing and managing data to allow a more precise and relevant data entry or filtering.
Example: Selecting a 'Genre' in the first drop-down can dictate the available 'Authors' in the next drop-down linked to that specific genre.
Creating a multiple dependent drop-down list in Google Sheets is helpful for organizing and filtering data. In our example below, we’ll show you how to sort books by various categories and how you can utilize the multiple dependent drop-down lists feature. Simply follow the steps below.
Populate your sheet with the categories ('Genre', 'Author', 'Year', 'Publisher') and their corresponding options. Place them in columns A, B, C, and D, starting from row 2 through row 5.
Navigate to cell A10, which will be your category selection cell, starting with 'Genre'. Select 'Data', then 'Data validation'.
Choose 'Add rule' within 'Data validation rules.'
Opt for 'Drop-down (from a range)' and input A1:D1 as your category range. Replicate this process for cells A11, A12, and A13.
Select cell B10 and set up data validation. This is where you'll view options relative to your selection in A10.
Implement data validation here, but leave the range undefined for now as it will vary depending on the A10 selection. Apply the same to cells B11, B12, and B13.
This formula ensures appropriate options appear in cells B10 to B13 based on selections in A10 to A13. Choose a range for the formula, such as E2:H5. In E2, use:
=ARRAYFORMULA(IF(LEN(A10:A13), TRANSPOSE(ARRAYFORMULA(IF(TRANSPOSE(A10:A13)=A1, INDIRECT("Genre"), IF(TRANSPOSE(A10:A13)=B1, INDIRECT("Author"), IF(TRANSPOSE(A10:A13)=C1, INDIRECT("Year"), INDIRECT("Publisher")))))), ""))
Alternatively, apply this template in E2 or your selected range:
=ARRAYFORMULA(IF(LEN(A10:A13), TRANSPOSE(ARRAYFORMULA(IF(TRANSPOSE(A10:A13)=[Category1 Cell], INDIRECT("[Named Range1]"), IF(TRANSPOSE(A10:A13)=[Category2 Cell], INDIRECT("[Named Range2]"), ...)))), ""))
Replace [Category1 Cell] and [Named Range1] with the respective category header cell and named range, and do so for each category.
To modify the data validation for B10, click the cell 'B10,' then the edit icon.
Select 'Drop-down (from a range)' and link it to E2:H2.
Follow this for B11 (E3:H3), B12 (E4:H4), and B13 (E5:H5). These ranges will adjust based on your A10 to A13 choices.
To ensure everything is set up correctly, choose 'Genre' in A10 and observe if B10 displays the correct genre options.
Repeat this for cells A11, A12, and A13 and check the corresponding B cells for accurate options.
We hope that you now have a better understanding of how to create a multiple dependent drop-down list in Google Sheets. If you enjoyed this article, you might also like our article on how to create a dependent drop-down list in Google Sheets or our article on how to Zoom in on Google Sheets.
