filtering a list to exclude 2 different dates
 

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

filtering a list to exclude 2 different dates
 

Hi Sarah

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

--
Regards
Roger Govier



"Sarah" wrote in message
...
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

filtering a list to exclude 2 different dates
 

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

"Roger Govier" wrote:

Hi Sarah

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

--
Regards
Roger Govier



"Sarah" wrote in message
...
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

filtering a list to exclude 2 different dates
 

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)

"Sarah" wrote in message
...
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

filtering a list to exclude 2 different dates
 

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
--
Regards
Roger Govier



"Sarah" wrote in message
...
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

"Roger Govier" wrote:

Hi Sarah

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

--
Regards
Roger Govier



"Sarah" wrote in message
...
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