i read some tutorials that help out with conditional (or dynamic) data validation, but its usually dynamically generating list item choices based on previous selection etc.
我阅读了一些有助于条件（或动态）数据验证的教程，但它通常根据之前的选择等动态生成列表项选项。
what i want is, lets say i have
我想要的是，让我说我有
columnA : columnB
telephone_number : 911
name: peterparker
is it possible for me to dynamically specify data validation rules on columnB based on what is selected in columnA? if its telephone_number, i want to check that the corresponding cell in columnB is a natural number, and if it is a name,i want to check for a string for a certain length. of course i limit the string options that the user can input in columnA.
我可以根据columnA中选择的内容动态指定columnB上的数据验证规则吗？如果是telephone_number，我想检查columnB中对应的单元格是自然数，如果是名称，我想检查一定长度的字符串。当然，我限制用户可以在columnA中输入的字符串选项。
2 个解决方案
#1
0
An alternative to data validation is Conditional Formatting. The benefit of the latter is that it is more versatile. We can have the following setting:
数据验证的替代方法是条件格式。后者的好处是它更通用。我们可以有以下设置：
Then if we define a name, DynamicValidation as follows:
然后，如果我们定义一个名称，DynamicValidation如下：
the formula at the validation box is
验证框中的公式是
=EVALUATE(SUBSTITUTE(VLOOKUP(Sheet1!$A2,Sheet1!$D$2:$E$3,2,0),"()","("&ADDRESS(ROW(),2)&")"))
EVALUATE cannot be used in any sheet cell, but it can be used in Named ranges. However, you will have to save your book as macro-enabled, because it is a legacy macro, originating from Excel 4.0. What it does is the following:
EVALUATE不能在任何图纸单元格中使用，但可以在命名范围中使用。但是，您必须将您的图书保存为启用宏，因为它是一个源自Excel 4.0的旧版宏。它的作用如下：
- Looks at the value in
List1and finds the formula that needs to be checked (VLOOKUP). - 查看List1中的值并找到需要检查的公式（VLOOKUP）。
- Subtitutes
"()"with the cell value of interest (and adds back the parentheses). - 用感兴趣的单元格值“（）”替换（并添加括号）。
Then if you open a conditional formatting box (Home tab, Conditional Formatting, New Rule, Use a formula to determine....) and input the formula =Not(DynamicValidation) like this:
然后，如果您打开一个条件格式框（主页选项卡，条件格式，新规则，使用公式来确定....）并输入公式= Not（DynamicValidation），如下所示：
then each cell that does not adhere to the formula will turn red. I tried that with data validation as well, but it does not work unless we build the depencency tree every time we change List 2.. I am not sure why.. But only conditional formatting works :) Here are some screenshots:
然后每个不遵守公式的细胞都会变红。我也试过了数据验证，但除非我们每次更改List 2时都构建了依赖树，否则它不起作用。我不知道为什么..但只有条件格式化才有效:)以下是一些截图：
The benefit is that we can change the validation criteria without hard-coding. I hope this helps!
好处是我们可以在不进行硬编码的情况下更改验证标准。我希望这有帮助！
#2
2
Following formula on custom validation rules will do:
以下关于自定义验证规则的公式将执行：
=IF(A1="telephone_number";ISNUMBER(B1);IF(A1="name";LEN(B1)=8;TRUE))
This is example for cell in B1. You may choose a wider range and combine $ to adapt you formula to all range.
这是B1中单元格的示例。您可以选择更宽的范围并结合使用以使您的公式适应所有范围。
#1
0
An alternative to data validation is Conditional Formatting. The benefit of the latter is that it is more versatile. We can have the following setting:
数据验证的替代方法是条件格式。后者的好处是它更通用。我们可以有以下设置：
Then if we define a name, DynamicValidation as follows:
然后，如果我们定义一个名称，DynamicValidation如下：
the formula at the validation box is
验证框中的公式是
=EVALUATE(SUBSTITUTE(VLOOKUP(Sheet1!$A2,Sheet1!$D$2:$E$3,2,0),"()","("&ADDRESS(ROW(),2)&")"))
EVALUATE cannot be used in any sheet cell, but it can be used in Named ranges. However, you will have to save your book as macro-enabled, because it is a legacy macro, originating from Excel 4.0. What it does is the following:
EVALUATE不能在任何图纸单元格中使用，但可以在命名范围中使用。但是，您必须将您的图书保存为启用宏，因为它是一个源自Excel 4.0的旧版宏。它的作用如下：
- Looks at the value in
List1and finds the formula that needs to be checked (VLOOKUP). - 查看List1中的值并找到需要检查的公式（VLOOKUP）。
- Subtitutes
"()"with the cell value of interest (and adds back the parentheses). - 用感兴趣的单元格值“（）”替换（并添加括号）。
Then if you open a conditional formatting box (Home tab, Conditional Formatting, New Rule, Use a formula to determine....) and input the formula =Not(DynamicValidation) like this:
然后，如果您打开一个条件格式框（主页选项卡，条件格式，新规则，使用公式来确定....）并输入公式= Not（DynamicValidation），如下所示：
then each cell that does not adhere to the formula will turn red. I tried that with data validation as well, but it does not work unless we build the depencency tree every time we change List 2.. I am not sure why.. But only conditional formatting works :) Here are some screenshots:
然后每个不遵守公式的细胞都会变红。我也试过了数据验证，但除非我们每次更改List 2时都构建了依赖树，否则它不起作用。我不知道为什么..但只有条件格式化才有效:)以下是一些截图：
The benefit is that we can change the validation criteria without hard-coding. I hope this helps!
好处是我们可以在不进行硬编码的情况下更改验证标准。我希望这有帮助！
#2
2
Following formula on custom validation rules will do:
以下关于自定义验证规则的公式将执行：
=IF(A1="telephone_number";ISNUMBER(B1);IF(A1="name";LEN(B1)=8;TRUE))
This is example for cell in B1. You may choose a wider range and combine $ to adapt you formula to all range.
这是B1中单元格的示例。您可以选择更宽的范围并结合使用以使您的公式适应所有范围。