Advanced Filter where criteria not met

[tanny81]

Hi All,

I am currently using an advance filter to show only records where data from my criteria range is present, however what I want to know is if I can do the opposite. So in essence I want to keep the same data in my criteria range, but only show records which don't meet this criteria.

Hopefully this makes sense.....

Thanks in advance

 

[BobUmlas]

Surround your current advanced filter formula with NOT:
=CurrentFormula
becomes
=NOT(CurrentFormula)

 

[tanny81]

Hi I've tried that, but I am getting a reference not valid error message.

For reference, I am filtering the list in place, my list range is Sheet1!$H:$H & my criteria range is Sheet2!$A$1:$A$26 This works fine, but when I change my criteria range to =not(Sheet2!$A$1:$A$26) I get the above mention error message.

Have I misunderstood something?

 

[BobUmlas]

No, I misunderstood. I thought you were using a formula for the advanced criteria. Do this, assuming you have column K available (arbitrary). In K2 (of sheet1) enter
=ISNA(MATCH(H2,Sheet2!$A$1:$A$26,0))
then when you use the advanced criteria, specify $K$1:$K$2 as the criteria range.