Thanks. Very helpful.

"Debra Dalgleish" wrote:

For an Advanced Filter, you can use a formula instead of specific
criteria, if you use a blank heading cell, or a heading that's not in
the main table.

The COUNTIF formula checks each row's entry in column G, to see if it's
in the MyList table. If it's not in the list, the count is zero, so that
row passes the filter test, and would be included in the output.

There are a few other examples of formulas he

http://www.contextures.com/xladvfilter02.html

Dom_Ciccone wrote:
Debra,

I just tried that myself and I can see it works, but can't quite get my head
around HOW it works. can you explain it please?

DC

"Debra Dalgleish" wrote:


Assuming you have a list of items in a range named MyList
For the criteria area, leave the heading cell blank, and in the cell
below, enter a formula that refers to the list of items, and the column
in the table that you want to check.

For example, if the column to check is column G, and the data starts in
row 2:

=COUNTIF(MyList,G2)=0

When you run the Advanced Filter, select both the blank heading cell,
and the cell with the formula, for the criteria range.

Stuart wrote:

Can I filter a group of items based on a list but return things NOT on the
list.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html