Archive of Mr Excel Message Board

Define E1:G3 as Criteria. Enter "CATG" into cells
E1:F1. Enter 1 into E2. Enter "ALL" into into F3.
Enter the formula, =PRODUCT(WEEK-{1,27})<=0 into
G3. This will select all category 1 records
and any "ALL" category records if their WEEK
value is between 1 and 27 inclusive. The array
constant can be replaced with an absolute
cell reference containing these values.

Now I'm getting "missing or illegal field name" errors. Might that have to do with having "CATG" in more than one column? (I replaced another field name [not used in the filter] with the "CATG" label in the Criteria range.)

The PRODUCT formula is showing a #VALUE error -- I know that doesn't always matter in an Advanced Filter, but since I can't get past the illegal field name, I don't know whether it works or not.

Thanks for the help -- I'm really pounding my head against the wall with this one.

Catherine, I used "CATG" because that was a
valid field name in your sample data (listed
below). Is it possible that your "live" data
doesn't include a "CATG" field?

It does. I guess my question comes down to how Advanced Filter handles column labels. In learning to use it, I always thought labels in Criteria had to be exactly the same as labels in Database, even for fields not containing criteria used in the filter.

i.e., to filter for the number "123" in column "AAA":

CRITERIA
AAA BBB CCC
123 --- ---

DATABASE
AAA BBB CCC
...data....

(the dashes are my attempt to make this line up properly. Wouldn't it be easier if this board used a monospace font?)

Now, you suggested that I [quote]"Enter "CATG" into cells E1:F1."[unquote]

If I understand what you were getting at, I end up with a filter that looks more like this:

CRITERIA
AAA AAA CCC
123 --- ---
--- ALL ---

DATABASE
AAA BBB CCC
...data....

I'm assuming that the mismatch of AAA and BBB is causing the field name errors, but I was trying to follow instructions and find out if Filter worked in a way I didn't know about.

It works if I do it this way, of course:

AAA BBB CCC
123 --- ---
ALL --- ---

The complication is in getting the second (date range) criteria to work.

AAA BBB CCCCC CCCCC
123 --- ----- -----
ALL --- ----- -----
--- --- >date <date

I've tried all permutations of this, with date formulas in every conceivable place in the criteria grid! :) I've tried including an AND statement to put < and > in the same formula.
I've also tried inserting a second, identical date column, and labeling them Begin Week and End Week so they would be filtered separately.

If I can learn what you're trying so patiently to show me, I think I can master several other hurdles I'm facing. Please help further if you can.

Thanks!
Catherine

p.s. I'm amazed at how much traffic this board has; I'm delighted to have found the place. I hope I may be able to contribute a few answers rather than questions once I hit this deadline... :)

That last one should be:

The complication is in getting the second (date range) criteria to work.

A1: AAA B1: BBB C1: CCCCC D1: CCCCC
A2: 123 B2: --- C2: ----- D2: -----
A3: ALL B3: --- C3: ----- D3: -----
A4: --- B4: --- C4: >date D4: <date

Don't know if that will be ANY clearer, but I'm trying!

> ...I always thought labels in Criteria had to
> be exactly the same as labels in Database...

You don't have to list all Database field labels
in the 1st row of the Criteria. Furthermore,
(this is IMPORTANT) the 1st row of a computed
criterion [ e.g, =PRODUCT(WEEK-{1,27})<=0 ]
MUST BE BLANK.

Take a look at the Excel Help topic for
"Examples of advanced filter criteria".

I thought you only wanted "ALL" if between
date1 and date2. If that's the case use...

A1: AAA C1: CCCCC D1: CCCCC
A2: 123 C2: ----- D2: -----
A3: ALL C3: > date1 D3: < date2

So list every "123" records and only "ALL"
records between date1 and date2