Data Validation list with filtered data

**rjtimmerman** · 01-17-2012, 04:24 AM

I would like to have a Data 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 Data 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.

**JBeaucaire** · 01-17-2012, 05:48 AM

You create a second range that uses formulas to make a list of the TRUE items. How are the TRUE/FALSE entries in column2 occurring? If that's a formula, we can edit that formula to help with this.... post up the formula in the first cell in that column.

**rjtimmerman** · 01-17-2012, 07:59 AM

Originally Posted by JBeaucaire

You create a second range that uses formulas to make a list of the TRUE items. How are the TRUE/FALSE entries in column2 occurring? If that's a formula, we can edit that formula to help with this.... post up the formula in the first cell in that column.

The TRUE/FALSE entries in Column2 is indeed a formula (=IF(COUNTIF(Sheet3!A:A;A2)>0;TRUE;FALSE)). It basically counts if there are occurences of the specific item in a range on Sheet3.

Can you explain how to create a range based on those formulas? I've attached a simple example of the situation.

example.xlsx

**JBeaucaire** · 01-17-2012, 12:05 PM

Like so... instead of TRUE/FALSE, we change that formula to give "unique numbers" in an index. Then we make a list up to the MAX of that index and then list those unique values in the final column. A dynamic named range MyList will grow/shrink on its own as items appear/disappear from that list. We use that named range for the SHeet1 Data Validation.