Excel数据验证列表使用公式进行过滤 [英] Excel Data Validation List using formula with filtering
问题描述
我有以下工作簿:
工作表帐户:
工作表帖子 :
我想知道是否可以使用公式将表格类型列表的数据验证定义为 B 列表帖子 数据验证弹出式窗口只显示帐户列表中的 ID ,其中网站列与所选行的网站列匹配帖子表格,其中状态列中的有效
ish或LINQ-ish单词:
SELECT ID FROM Accounts WHERE Website =
@ SelectedPostRow.Website AND Status = Active
第二张图片上的标记显示哪些值应在下拉列表中显示。
解决方案这个问题需要一点准备。
在同一张表中,或在另一张表中复制您的数据(或添加相关单元格)
在列A中,您可以排列一个数字。
我得到它(单元格A2):
= IF(C2 = $ J $ 2,1,0)* IF (E2 =Active,1,0)* ROW()
在列B中排列数字并排除不需要的行B2):
= IF(A2 = 0,0,RANK(A2,A:A))
然后,您可以使用列G(手动输入)中的列H中的VLOOKUP。 H1的公式:
= IFERROR(VLOOKUP(G2,$ B $ 2:$ D $ 9,3,FALSE),)
现在您可以根据列H设置验证
PS:公式中可能会出现小错误因为我已经将它们从意大利语翻译出来,我不能用英文测试。
I have following workbook:
Worksheet Accounts:
Worksheet Posts:
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?
In SQL-ish or LINQ-ish words:
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)
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()
In column B rank the number and exclude the unwanted rows (B2):
=IF(A2=0,0,RANK(A2,A:A))
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),"")
Now you could set your validation based on column H
PS: there could be small errors in the formulas as I have translated them from italian and I cannot test in english.
这篇关于Excel数据验证列表使用公式进行过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!