忽略数据验证中的公式
我正在寻找一个可以在数据验证中执行以下功能的公式
检查是否有重复的数字
如果单元格 B2（例如）包含单词“继续”，则忽略重复检查。
为了解释它的用途，我有一本日志，我的人员将其记录在我们被派去的每次呼叫中。它们被标记为 22-0001、22-0002、22-0003 等。有时，我的人员会输入错误并添加已创建的电话号码，最终得到 22-0001、22-0002、22-0002、22-0003。这最终导致我们的州报告偏离，我们的数据也与我们的调度显示的不同。这就是事情变得棘手的地方。允许使用重复呼叫号码的情况有一个例外，即当他们选择“继续”作为呼叫类型时。如果响应的单位数量多于一个日志表格所能容纳的数量，我们就会进行此选择。我们希望能够输入相同的呼叫号码，以便我们知道“继续”指的是哪个呼叫，以防我们需要在日志中回溯。
我弄清楚了如何检查重复项，我唯一的问题是如果单元格包含上述文本，则忽略公式。
这是日志设置方式的屏幕截图，以供参考。红色指的是他们输入索书号的单元格，以及我想阻止他们输入重复项的单元格。橙色是我希望函数引用的单元格，以便查看是否选择了“继续”性质。如果选择它，我希望第一个函数防止重复输入被忽略，因为它仍然是同一索书号的一部分。
I am looking for a formula that would perform the following functions within data validation
Check for duplicate numbers
Ignore checking for duplicate if cell B2 (for example) contains the word "Continued".
To give explanation of what it is for, I have a logbook that my personnel puts in each call we are dispatched out to. They are labeled 22-0001, 22-0002, 22-0003, etc. Sometimes my personnel mistypes and adds a call number already created and we end up with 22-0001, 22-0002, 22-0002, 22-0003. This ends up throwing off our reports for the state and our numbers are then off from what our dispatch shows. This is where it then gets tricky. There is one exception for when a duplicate call number is allowed, and that is when they select "Continued" as the call type. We have this selection for if we have more units responding than can fit in one log form. We want to be able to put the same call number so we know which call the "Continued" is referring to, in case we need to backtrack in logs.
I figured out how to check for duplicates, my only issue is getting the formula to be ignored if the cell contains the text stated above.
Here is a screenshot of how the logbook is set up for reference. The red refers to the cell they input the call number, and the cell I want to prevent them from being able to type duplicates. And the orange is the cell I would like the function to refer to in order to see if the nature "Continued" is selected. If it is selected, I would like the first function preventing duplicate input to be ignored since it would still be part of the same call number.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以使用辅助列（请参阅屏幕截图列副本），然后添加条件格式。或者您在条件格式中使用公式。
公式首先计算第一列中当前行呼叫号码的所有出现次数 - 但前提是类型 <> “持续”。
（因为有两个条件，所以需要
countifs。如果出现多次，则在辅助列中放置 X。
如果不使用表格，则必须替换 [callnumber] 当前行的特殊号码。
[@callnumber] 指的是 图像src="https://i.sstatic.net/hIjqt.png" alt="在此处输入图像描述">
You can either use a helper column (see screenshot column duplicate) and then add conditional formatting. Or you use the formula within your conditional formatting.
Formula first counts all occurences of the callnumber of the current row within the first column - but only if type <> "continued".
(As there are two conditions, you need
countifs.If it occurs more than once an X is placed in the helper column.
If you don't use a table, then you have to replace [callnumber] by the column where you have your numbers. [@callnumber] refers to the special number of the current row.