Dynamic drop-down lists in Google Sheets

2
votes

i want to know how to make dynamic drop down list in Google Sheets, e.g I have a list of continents in "Continent" column (data validation applied) and want to validate the next column with only countries that fall on previous selected continent. Example sheet

It would be helpful to solve this without script. If it required script please explain in detail.

1
google-sheets

1 Answers

1
votes

The way to do this without a script involves a helper list. Here are the steps:

  • Create Named Ranges for Each of Your Lists
  • Link a Column to the first dropdown using ARRAYFORMULA and INDIRECT
  • Set data validation of the second dropdown to that entire column being populated by ARRAYFORMULA(INDIRECT())

enter image description here

You can see the working example here: https://docs.google.com/spreadsheets/d/1Snn2ZpDTpb1I96ihsRHcUqvgsijiHPQNqqRD9bc-1_A/edit?usp=sharing