CODEWITHSUNDEEP
excel-2007excel-formula
Carpenter
Carpenter

Reputation: 13

Excel 2007: Data Validation list with filtered data

I would like to have a Validation List in cell D5 on Sheet1. The items in this list should be dynamically based on a table on Sheet2, but I only need the ones where Column2 is TRUE. Below is an example of my table on Sheet2:

Column1 | Column2
Item1   | TRUE
Item2   | TRUE
Item3   | FALSE
Item4   | TRUE

Based on the tabel above the Validation List in cell D5 on Sheet1 should only contain Item1, Item2 and Item4. Is this somehow possible (preferably without using VBA)?

I know I can create dynamic Named Ranges, but I don't know how to filter them, based on another column.

Upvotes: 0

Views: 1318

Answers (1)

DanB
DanB

Reputation: 96

You probably want to create an index to filter out the false.

Easiest way would be to flip your columns, or make column 3 a copy of column 1, then use a formula like.

Column1 | Column2 | Column3 Item1 | TRUE | Item1 Item2 | TRUE | Item2 Item3 | FALSE | Item3 Item4 | TRUE | Item4

{=INDEX($B$1:$C$100,SMALL(IF($B$1:$C$100="TRUE",ROW($B$1:$C$100)-ROW($B$1)+1,ROW($C$100)+1),1),2)}
    {=INDEX($B$1:$B$100,SMALL(IF($B$1:$C$100="TRUE",ROW($B$1:$C$100)-ROW($C$1)+1,ROW($C$100)+1),2),2)}

Those would be your first 2 fields, they create an index that gives you the 1st and 2nd result with TRUE. The 2nd number at the end of the formula determines which item in the list. when you enter the formula, you can't just type it in with the { }, leave those off and press ctrl+shift+enter to complete the formula and it will put them in for you, this is vital to creating an index formula.

Once you have the index created, it will update based on the true/false values. If you then use that range in a data validation dropdown, it will update with the index.

Upvotes: 0

Related Questions