agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

HansV
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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.
Best wishes,
Hans

agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Thank you. I thought that any numerical value other than zero was 'true'? Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

HansV
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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.
Best wishes,
Hans

agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND