允许例外进行相关(辅助)数据验证 [英] Allow exception for dependent (secondary) data validation

问题描述

我正在尝试制作一张表格，该表格将向用户提供在报告客户投诉时可以选择的可能缺陷的列表.首先，他们将从存在问题的产品区域列表中进行选择，然后从可能出现的缺陷列表中进行选择，这些缺陷将根据在第一个列表中选择的位置进行填充.我已经尽可能详尽地列出了区域列表，但是我觉得如果我没有预见到这个问题的可能性，可能会出现一些问题.因此，我想包括在位置下拉列表选择，选择的时候，会从数据验证列表中的第二数据入口点更改为允许用户典型什么用户想添加一个其他"选项.数据验证的类型是否可能取决于条件?

I am trying to make a form that will give users a list of possible defects to choose from when reporting a customer complaint. First they will choose from a list of regions on the product where the problem exists and then they will choose from a list of possible defects which will populate based on the location selected in the first list. I have made the list of regions as comprehensive as possible but I feel that it is likely that something will arise where I have not forseen this issue as a possibility. As such I would like to include an "Other" option in the location drop down list which, when selected, would change the second data entry point from a data validation list to allowing the user to typ anything the user would like to add. Is this possible to have the type of data validation dependent on a condition?

推荐答案

从属下拉列表列出了验证.参考youtube视频一个和

Dependent dropdown lists validation. Reference youtube video one and two

您可以在源列表顶部提到其他"选项，以进行第一次(位置)验证.然后，您可以使用带偏移功能的命名范围为第一个列表添加数据验证.这样一来，您就可以在列表底部自由添加其他位置.

You can mention "Others" option on the top of the source list for the first (Location) validation. You can then add data validation for the first list using named range with offset function. This will allow you to freely add other locations at the bottom of the list.

对于第二列验证，您可以输入公式，以跳过位置列表中的第一个(其他")单元格.

For the second column validation, you can enter formula skipping the first ("Others") cell in the location list.

在下图中，D列的验证源列表为$ A $ 2:$ A $ 10，E列的验证列表公式为=IF(COUNTIF($A$3:$A$10,$D2)>0,$B$2:$B$10,$E2)

In the image below, validation source list for column D is $A$2:$A$10 and validation list formula for Column E is =IF(COUNTIF($A$3:$A$10,$D2)>0,$B$2:$B$10,$E2)

这篇关于允许例外进行相关(辅助)数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！