Google表格中的相关数据验证 [英] 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.
因此，问题是我有两列包含数据验证列表.第二列以第一列信息为基础，即，单元格中的第一列是否具有下拉列表中的"Fruit".和蔬菜".我将选择水果"并且直接在其右侧的单元格将提供预定水果的下拉列表，或者如果"Vegetable"是选择后，将出现蔬菜列表.我能够使用此函数=TRANSPOSE(FILTER($R$14:$S,$R$13:$S$13=H14))，其中R14:S是范围，而R13:S13是标题/水果"和蔬菜".这给了我一个基础，可以作为我第二篇专栏的基础.没问题，但问题是，我不能只是从上方抓取经过数据验证的范围并将其向下拖动.我必须进入每个单元格，然后将该列的范围向下更改为该列中所有单元格的每一行.
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.
我还尝试了使用此公式=INDIRECT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)))的公式通过数据验证.这个想法是，无论直接位于左侧的单元格是什么，都会出现一个基于该名称的列表，但这是行不通的.
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.
我愿意尝试脚本；我对VBA有一定的了解，但是没有编写脚本的知识，因此我肯定需要一些指导.预先感谢您的帮助.
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.
推荐答案
您无法以尝试的方式进行多个动态相关的下拉菜单. (拖放).您将需要一个脚本来执行此操作.
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.
这篇关于Google表格中的相关数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
