Data Validation list with filtered data

[rjtimmerman]

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:

Please Login or Register  to view this content.

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]

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]

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]

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.

[rjtimmerman]

Thanks! That works beautifully!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.