filtering a list to exclude 2 different dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large data-base type spreadsheet where each line has a due date.

In 2 cells above the data, I have due dates that change every month, the
user types in the 2 due dates that change monthly.

I would need to filter the records to exclude these 2 dates.

The user should not have to create a filter himself, only change the dates.

Using auto-filter, the problem is that I cannot use "does not equal" with a
cell reference.

Using advanced filter, it does not recognise <>(H5)

Any suggestions?

Many thanks
Sarah
 
Hi Sarah

It sounds as though the test should be <$H$2 or <>$H$3 rather than the
formula you have
 
hi Roger,

Thanks for your answer but the issue is not with a cell being locked or not,
and <$H$5 would anyway not exclude the date, it would take all those smaller
than.... but I wouldn't get the ones bigger than.... And I need both, i.e. I
need to exclude from a list of dates comprising of all dates possible in a
month, 2 dates, i.e. 10th September and 15th September, per ex. that would
be amendable.

My formula would refer to these 2 changing cells.

I'm stuck because auto-filter doesn't recognise cell references and advanced
filter doesn't recognise

Due Date
<>$H$5

I tried an IF but when I put <>H5 in the criteria, it returns #REF

Anybody has an idea how I can proceed?

Thanks

Sarah
 
Perhaps a helper column?

A1: Flag
A2: =IF(OR(B2=$H$5,B2=$H$6),"Skip","Show")
Copy A2 down as far as needed.

Then, with AutoFilter engaged
.....Click on the Flag dropdown, in A1, and select "Show".

All of the Skip rows will be hidden.

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Hi Sarah

Advanced Filter will work.
In your criteria cell enter ="<>"&H5
Lets say your criteria cells for the Advanced filter are M1:N2
Due Date Due Date
="<>"&H5 ="<>"&H6
Would filter the data and exclude values where the date equals the date in
H5 and the Date in H6

Change "<>" to ">" or "<" would allow you to filter for dates inclusive
within a range
 
Back
Top