代码之家 › 专栏 › 技术社区 › Gustavo Baiocchi Costa

Excel数据验证作为另一个数据验证的输入

dynamic validation list excel

Gustavo Baiocchi Costa · 技术社区 · 12 年前

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

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

我有一个数据验证列表,允许用户选择部门。如果用户选择1,那么在另一个数据验证列表中,我要列出Gustavo和Paul。如果用户选择2,那么在另一个数据验证列表中,我要列出John、Jack和Simona。此外,数据可能会扩大。我的意思是:也许在Simona下面可以添加另一个用户,比如说:Berry 1。然后,如果用户选择1,则Gustavo、Paul和Berry将是其他数据验证列表的选项。

我已经实现了第一个验证列表。我遇到的问题是问题的第二部分。大多数解决方案都使用名称管理器。这对我来说是一个问题,因为我的数据布局方式以及我需要不断更新名称管理器。我想让它更动态,当用户添加或删除数据时,它总是显示该部门的当前列表。之后我会有第三个验证列表,但是,如果我能学会如何做这个,那么我应该能够解决剩下的问题。

1 回复 | 直到 11 年前

Captain 12 年前

好的,所以您可以通过几个步骤来完成这一操作,并使用一个工作范围来保存验证列表:

在工作簿的某个位置,可以在多个单元格中创建数组公式
- 选择单元格,例如F2:F6(我在示例中使用了每个部门5个可能的经理,但您可以更改)
- 选择所有选项后,输入ARRAY公式(即使用 Ctrl键 + 转移 + 进来 输入它) =SMALL(IF($B$2:$B$6=$D$4,ROW($B$2:$B$6),""),ROW(INDIRECT("1:5"))) -经理的部门在B2:B6-选定的部门在D4 1:5 是一个有效的计数器,向我们的5个可能的经理。
- 这将为您提供一个行号列表,其中Division与所选的相同,然后是一些 #NUM! 错误
在下一列(G2:G6)中输入(正常)公式 =IFERROR(INDIRECT("A"&F2),"") 。这将为您提供所选部门经理的姓名列表。
在要选择的单元格中,使用源 =OFFSET($G$2,0,0,COUNT($F$2:$F$6)) -这引用了名称列表,但去掉了底部的空白