Excel数据验证作为另一个数据验证的输入 [英] Excel Data Validation as input to another Data Validation

问题描述

假设我有一个包含以下信息的工作表:

Assuming I have a worksheet with the following information:

Manager    Division
Gustavo        1
John           2
Jack           2
Paul           1
Simona         2

我有一个数据验证列表，允许用户选择一个部门. 如果用户选择1，则在另一个数据验证列表中，我要列出Gustavo和Paul.如果用户选择2，则在另一个数据验证列表中，我要列出John，Jack和Simona. 此外，数据可能会扩展.我的意思是:也许可以在Simona下方添加另一个用户，例如:Berry1.然后，如果用户选择1，则Gustavo，Paul和Berry将成为其他数据验证列表的选项.

I have a data validation list that allows the user to select a division. If the user selects 1, then in another data validation list I want to list Gustavo and Paul. IF the user selects 2, then in another data validation list I want to list John, Jack and Simona. Moreover, the data might scale. What I mean is: maybe below Simona another user can be added, let's say: Berry 1. Then if the user selects 1, then Gustavo, Paul and Berry will be the options for the other data validation list.

我已经实现了第一个验证列表.我遇到的问题是问题的第二部分.那里的大多数解决方案都使用名称管理器.这对我来说是个问题，因为我的数据布局方式以及我需要不断更新名称管理器.我想使其更具动态性，当用户添加或删除数据时，它总是显示该部门的当前列表.之后，我将获得第三个验证列表，但是，如果我可以学习如何进行验证，那么我应该能够解决其余问题.

I have already implemented the first validation list. The problem I am having is with the second part of the problem. Most solutions out there uses name managers. This is a problem for me because the way my data is laid out and because I need to keep constantly updating name managers. I would like to make it more dynamic, when a user adds or remove data, it always shows the current list for that division. I will have a third validation list afterwards, however, if I can learn how to do this one, then I should be able to solve the rest of the problem.

推荐答案

好，因此您可以通过几个步骤来完成此工作，并在一定范围内保存验证列表:

OK, so you can do this in a couple of steps with a working range to hold the validation list:

1. 在工作簿中的某个地方，您可以在多个单元格中创建一个数组公式
   • 选择单元格，例如F2:F6(在示例中，我在每个部门使用5个可能的Managers，但是您可以更改它)
   • 选中所有它们，然后输入ARRAY公式(即使用 Ctrl + Shift + Enter 输入)=SMALL(IF($B$2:$B$6=$D$4,ROW($B$2:$B$6),""),ROW(INDIRECT("1:5")))-经理的部门在B2:B6中-选定的部门在D4中... 1:5实际上是一个计数器，可以上升到我们的5位可能的经理.
   • 这应该为您提供行号列表，其中分区"与所选的相同，然后出现一些#NUM!错误

1. Somewhere in your workbook, you create an array formula in multiple cells
   • select cells e.g. F2:F6 (I'm using 5 possible Managers per division in the example, but you can change that)
   • With them all selected, enter the ARRAY formula (i.e. use Ctrl+Shift+Enter to enter it) =SMALL(IF($B$2:$B$6=$D$4,ROW($B$2:$B$6),""),ROW(INDIRECT("1:5"))) - the managers' divisions are in B2:B6 - the selected one is in D4... the 1:5 is a effectively a counter, going up to our 5 possible managers.
   • This should give you a list of row numbers where the Division is the same as selected and then some #NUM! errors

希望这很有道理！这是布局的图片:

Hope this makes sense! Here is a picture of the layout:

这篇关于Excel数据验证作为另一个数据验证的输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！