Problem with advanced filter

[Paul Smith]

Dear All

At the address

http://phhs80.googlepages.com/filter.xls

is an Excel file to show a problem with an advanced filter (with
criterion in F1:F2. My question is why does not the filter work?

Thanks in advance,

Paul

 

[Debra Dalgleish]

Some people won't be willing to download a file from an unknown web
site. If you describe the data, the criterion, the results you're
getting, and the results you you expect to get, someone may be able to help.

 

[Paul Smith]

At the address

http://phhs80.googlepages.com/filter.xls

is an Excel file to show a problem with an advanced filter (with
criterion in F1:F2. My question is why does not the filter work?

Sorry, consider instead the file at

http://phhs80.googlepages.com/filter1.xls

The database is in sheet 1, whereas the criterion is in sheet 2. I have
noticed that if I use "Date of Entry" without spaces, i.e.,
"DateofEntry", the advanced filter works fine. Is this a bug of Excel?

Paul

 

[Paul Smith]

Some people won't be willing to download a file from an unknown web
site. If you describe the data, the criterion, the results you're
getting, and the results you you expect to get, someone may be able to help.

Oh, I see! Thanks for explaining that to me.

Then consider the following database located in Sheet 1:

Name Local Date of Entry
John Toronto 3/21/04
Charles Paris 2/22/04
Peter Manchester 5/26/03

Consider that the criterion is in Sheet 2 and corresponds to:

=month(Sheet1!Date of Entry)=3

If you apply an advanced filter, you will no get any record as a
result, and the first record should be shown. If you use "DateofEntry"
(notice the nonexistence of spaces), the first recorded is shown. Is
this a bug of Excel?

Paul

 

[Debra Dalgleish]

I wouldn't call it a bug, just an unavailable feature. You can use one
word field names in the criteria formula, or just refer to the first
data cell in the column.

 

[Paul Smith]

I wouldn't call it a bug, just an unavailable feature. You can use one
word field names in the criteria formula, or just refer to the first
data cell in the column.

Thanks, Debra. It is however intriguing that if I put the criterion in
the same sheet where the database is located and I use the criterion

=month(Date of Entry)=3

(notice the elimination of "Sheet1!"), everything works fine.

Paul

 

[Debra Dalgleish]

You can use the row or column label in a formula on the same sheet, if
you have the 'Accept labels in formulas' feature enabled
(Tools>Options, Calculation tab).