CODEWITHSUNDEEP
excelexcel-formula
Ross
Ross

Reputation: 2141

Data Validation List filtered by row

Example of the data:

  A      B
Item 1  TRUE
Item 2  FALSE
Item 3  TRUE
Item 4  FALSE

I want to create Data Validation list using formulas ONLY (no macros) that will return only items from column A that have TRUE in column B. In this example the List would contain Item 1 an Item 3. I tried the following formulas for the list (based on this question) with no luck.

Excel complains that The Source currently evaluates to an error with this formula:

=IF(B1:B4=TRUE,A1:A4,"")

This causes no errors but the returned list only contains Item 1:

=INDEX(A1:A4,SUMPRODUCT(MATCH(TRUE,(B1:B4)=TRUE,0)),1)

Should be something very simple, but no luck so far.

Upvotes: 0

Views: 258

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

In C1 enter:

=IF(B1,1,"")

In C2 enter:

=IF(B2,1+MAX($C$1:C1),"")

and copy down. In D1 enter:

=IFERROR(INDEX(A:A,MATCH(ROW(),C:C,0)),"")

and copy down.

enter image description here

Then use column D for your DV list.

Upvotes: 1

Related Questions