使用带过滤的公式的 Excel 数据验证列表 [英] Excel Data Validation List using formula with filtering

问题描述

我有以下工作簿:

工作表帐户:

工作表帖子:

我想知道是否可以使用公式将 List 类型的数据验证定义到表 帖子 中的整个列 B，以便数据验证弹出窗口仅显示帐户表中的帐户ID，其中网站列与帖子中选定行的网站列匹配strong> 表，哪些在 Status 列中具有 Active 值?

I would like to know if it is possible to define Data Validation of type List to entire column B in sheet Posts using formula, so that Data Validation popup shows only Account Ids from Accounts sheet which Website column matches Website column of a selected row in Posts sheet and which have value Active in Status column?

在 SQL-ish 或 LINQ-ish 中:

In SQL-ish or LINQ-ish words:

SELECT Id FROM Accounts WHERE Website =@SelectedPostRow.Website AND St​​atus = Active

SELECT Id FROM Accounts WHERE Website = @SelectedPostRow.Website AND Status = Active

第二张图片上的标记显示应在下拉列表中显示哪些值.

Marks on the second image shows which values should be shown in a drop-down.

推荐答案

这个问题需要一点准备.在同一个工作表或另一个工作表中，复制您的数据(或添加相关单元格)

This problem need a bit of a preparation. In the same sheet, or in another, copy your data (or add the relevant cells)

在 A 列中，您有一个可以进行排名的数字.我用(单元格 A2)获得它:

In column A you have a number that could be ranked. I obtained it with (cell A2):

=IF(C2=$J$2,1,0)*IF(E2="Active",1,0)*ROW()

=IF(C2=$J$2,1,0)*IF(E2="Active",1,0)*ROW()

在 B 列中对数字进行排名并排除不需要的行 (B2):

In column B rank the number and exclude the unwanted rows (B2):

=IF(A2=0,0,RANK(A2,A:A))

=IF(A2=0,0,RANK(A2,A:A))

然后您可以使用列 G 中的枚举(手动输入)在 H 列中进行 VLOOKUP.H1的公式:

Then you can VLOOKUP in column H, using a enum in column G (manually entered). The formula for H1:

=IFERROR(VLOOKUP(G2,$B$2:$D$9,3,FALSE),"")

=IFERROR(VLOOKUP(G2,$B$2:$D$9,3,FALSE),"")

现在您可以根据列 H 设置验证

Now you could set your validation based on column H

PS:公式中可能有小错误，因为我是从意大利语翻译过来的，我无法用英语测试.

PS: there could be small errors in the formulas as I have translated them from italian and I cannot test in english.

这篇关于使用带过滤的公式的 Excel 数据验证列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！