CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
A Cohen
A Cohen

Reputation: 456

Dependent Data Validation in Google Sheets

I have been banging my head with this problem for a while now and found a temporary solution but it is so incredibly tedious for as many rows of data as I plan to have.

So, the issue is that I have two columns with lists of data validation. The second column is predicated on the first column information, i.e. if the first column in cell one will have a dropdown list of "Fruit" and "Vegetable". I will select "Fruit" and the cell directly to the right of it will provide a dropdown list of predetermined fruits, or if "Vegetable" was selected, a list of vegetables will appear. I was able to use this function =TRANSPOSE(FILTER($R$14:$S,$R$13:$S$13=H14)), R14:S being the range and R13:S13 being the headers/"Fruit"and "Vegetable" which gave me a range to base my second column on. Which worked alright, the issue is, I can't just grab the data validated range from above and drag it down. I have to go into each cell and change that list range down one row to each cell for all the cells in the column.

I also tried data validation by a formula using this formula =INDIRECT(INDIRECT(ADDRESS(ROW(),COLUMN()-1))). The idea being whatever the cell directly to the left equals, a list based on that name will appear, however, that didn't work.

I am willing to try scripts; I have some knowledge of VBA, but not writing scripts, so I would definitely need some guidance. Thanks in advance for your help.

tracker and 2nd dropdown

All lists

https://docs.google.com/spreadsheets/d/1pW1DcIVZ5IzxHYMujNRTbIxegvbiPZwQ3FDsztYSOtc/edit?usp=sharing

Upvotes: 0

Views: 2151

Answers (1)

Aerials
Aerials

Reputation: 4419

You can't do multiple dynamic dependent drop-downs the way you are trying to do. (Dragging and dropping). You will need a script to do this.

In this help article there is a sample sheet where a user shares a script to build dynamic dependent drop-downs. It is quite powerful and perhaps it will be useful to you.

Upvotes: 2

Related Questions