Excel:使用 [column_A 的唯一单元格，其中 column_B 值 = SomeValue] 填充下拉列表

Question

我对 Excel 很陌生，但我有以下情况:

我有 Sheet1，其中包含:

column_A 中用户输入的数值

column_B 中用户选择的是/否下拉列表，它引用隐藏的 Sheet3 上的两项列表 (YES,NO) .

我有 Sheet2其中包含:

用户选择的下拉列表，包含 Sheet1:column_A 中的所有唯一值对于用户选择在 Sheet1:column_B 中的 YES/NO 下拉菜单中选择“YES”的行.

到目前为止，我有一个命名列表 Column_A_Values使用以下公式:

=OFFSET(`Sheet1`!$A$1,0,0, COUNTA(Sheet1!$A$1:$A$1000)+99)

在一个隐藏的 Sheet3 ，我在 Sheet3!column_B 中有以下公式它尝试生成一个唯一值列表，其中用户选择在 Sheet1:column_B 中的 YES/NO 下拉列表中选择“YES” .这不起作用，但我相信它在正确的轨道上:

=INDEX(Column_A_Values,MATCH(0,COUNTIF($B$1:$B15,Column_A_Values)+("YES"<>Sheet1$B15),0))

最后，我在 sheet2 上有一个下拉列表，引用了一个包含以下公式的命名列表:

=OFFSET(Sheet3!$B$2, 0,  0, COUNT(IF(Sheet3!$B$2:$B$1000="", "", 1)), 1)

如果有任何不清楚的地方，我深表歉意，并会在必要时进行澄清。我将不胜感激任何人愿意提供的帮助。我无法让第二个公式正常工作，所以这是我的首要任务，但我也很感谢您对获得 Sheet2 的项目符号功能的更好方法的任何建议。 .再说一次，我对 Excel 很陌生，但我边走边学。

Answer 1

如果您不想使用 Ctrl+Shift+Enter 数组公式类型的解决方案，您可以尝试:

1 指数 - SumProduct 公式

=IFERROR(INDEX(Column_A_Values,(SUMPRODUCT(SMALL(--(Column_B_Values="YES")*ROW(Column_A_Values),COUNTIF(Column_B_Values,"<>"&"YES")+ROW(A1))))),"")

但是，如果您是否使用数组公式无关紧要，这里有一些其他功能，作为与亲爱的@ 不同的选项A.S.H 已建议。

2 指数公式 - 阵列系列

=IFERROR(INDEX(Column_A_Values,(SMALL(IF(Column_B_Values="YES",ROW(Column_A_Values)),ROW(A1)))),"")
Don't Forget to End your function by clicking ctrl+shift+enter, that's really important.

3 --- 偏移公式 - 阵列系列

=IFERROR(OFFSET($A$1,SMALL(IF(Column_B_Values="YES",ROW(Column_B_Values)-MIN(ROW(Column_B_Values))+1),ROWS($1:1))-1,0),"")
Don't Forget to End your function by clicking ctrl+shift+enter, that's really important.

**In these examples the only thing you need to do is defining Column_B_Valuesas your Yes/No Column in Sheet1 - Column_B **

然后正如@A.S.H 再次提到的那样，您的验证命名列表可以设置为以下公式:
=OFFSET(Sheet3!$B$2,0,0, COUNT(Sheet3!$B:$B))