Excel: Populate drop-down with [unique cells of column_A, where column_B value = SomeValue]

1
votes

I'm very new to Excel, but I have the following situation:

I have Sheet1 which contains:

  • user-entered numeric values in column_A
  • user-selected YES/NO dropdown list in column_B, which references a two-item list (YES,NO) on a hidden Sheet3.

I have Sheet2 which is to contain:

  • A user-selected drop-down containing all unique values in Sheet1:column_A for rows where the user has chosen to select "YES" to the YES/NO dropdown in Sheet1:column_B.

So far, I have a named list Column_A_Values with the following formula:

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

On a hidden Sheet3, I have the following formula in Sheet3!column_B which attempts to generate a list of unique values where the user has chosen to select "YES" to the YES/NO dropdown list in Sheet1:column_B. This doesn't work, but I believe it's on the right track:

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

Finally, I have a dropdown list on sheet2 referencing a named list containing the following formula: 

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

I apologize if any of this isn't clear, and will clarify wherever necessary. I would appreciate any assistance anyone is willing to provide. I have not been able to get the 2nd formula working correctly, so that is my priority, but I am am also grateful for any advice you might have regarding superior approaches to obtain the bulleted functionality for Sheet2. Again, I am very new to Excel, but I'm learning as I go.

2
excelexcel-formula

2 Answers

2
votes

If you don't want to use Ctrl+Shift+Enter Array Formula Type of Solution you can try:

1 Index - SumProduct Formula

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

But if it doesn't matter for you whether go by Array Formula or not, here are some another functions, as a different options than what dear @A.S.H has suggested.

2 Index Formula - Array Series

=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 --- Offset Formula - Array series

=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 **

Then as @A.S.H has mentioned again, your named list for the validation can be set to this formula:

=OFFSET(Sheet3!$B$2,0,0, COUNT(Sheet3!$B:$B))

2
votes

To generate the list of unique values from Column_A_Values in column B of Sheet3, type this array formula at Sheet3!B2 then copy/paste down the column for a sufficient number of rows:

Sheet3!B2:
=IFERROR(INDEX(Column_A_Values, MATCH(1,
  (OFFSET(Column_A_Values,0,1)="Yes")*(COUNTIFS(B$1:B1,Column_A_Values)=0),
   0)),"")

Ctrl+Shift+Enter

Finally, your named list for the validation can be set to this formula:

=OFFSET(Sheet3!$B$2,0,0, COUNT(Sheet3!$B:$B))