Advanced Filter with array

[agibsonsw]

Hello. (2003)
Is it possible to use an array formula as a calculated criteria for Advanced Filter? I've array entered this formula as a criteria:
=SUM(IF(VLOOKUP($A6,staff3,COLUMN(B6:J6),0)<>B6:J6,1,0))
'staff3' is a table on a separate worksheet.
The formula returns TRUE if a value in column B to J differs to the corresponding value in table 'staff3', and it works if I drag it down a column.

(This follows on from a recent question I've asked, but I now just need to know if the Advanced Filter can understand an array formula.)
Thanks, Andy.

[HansV]

Your formula doesn't return TRUE or FALSE, it returns the number of differences. If you enter a column heading (field name) above the formulas and use the same column heading in the criteria range, you can filter for a specific number of differences, for example 0 if you want to see only the records with no differences, or >0 if you want to see only the records with differences.
But since the formula doesn't return TRUE or FALSE, you can't use it in a calculated condition in the Criteria range without a column heading.

[agibsonsw]

Thank you. I thought that any numerical value other than zero was 'true'? Andy.

[HansV]

That's true, but it doesn't work for calculated criteria as far as I can tell, I think you'll need to add a calculated column to your table.

[agibsonsw]

Hi.
I think you're right. I tried wrapping an IF function to return a value of TRUE or FALSE, But I'm getting the impression that Advanced Filter cannot handle an
array formula. Andy.