Dependent Data Validation in Google Sheets

0
votes

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

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Can you share a copy of your sheet? Remove sensitive information. The idea is to see what your desired result looks like. - Aerials
@Aerials - I have added images to assist, hope it helps. - A Cohen
The images are not nearly as useful as a sample spreadsheet, showing your starting data, and what you would like the outcome to look like. Otherwise everyone trying to help you has to make a sample sheet to try answer your question. - kirkg13
@kirkg13 - Just added a sample worksheet, hope that helps. - A Cohen
I think I've seen the answer to your issue. Google "Smart Data Validation create dependent" and you'll find a long article by MAXIMMAKHROV who provides details on how to use different validation ranges, and changing the pointer to them based on the value selected for your first cell. Hope this helps. If I get to it, I will write it up for an answer. - kirkg13

1 Answers

2
votes

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.