Advanced Filter : Computed Criteria

[Spreadsheet Solutions]

Dear all;

When using computed criteria with Advanced Filtering, formula's can return
an error value.
It works fine, so no prob's. but the reason for this is not clear to me.

Who can explain why filtering still works if the outcome of a formula is an
error value.
It just don't make sense to me.

 

[Debra Dalgleish]

When you use a formula in the criteria, you refer to the first row in
the source table. The result in that row could be an error, e.g.:

=SEARCH("Pen",D2)

and that would show in the criteria area. However, other rows might
return a number, and those rows would be shown in the filtered data.

 

[Tom Ogilvy]

I would suspect that the code that executes the filter looks at the formula
and evaluates it for each row - it doesn't look at the results of the
prototype in the criteria range. It might be clearer if the formula were
entered as a text string, but then how would it know that it is a formula
rather than a literal string to use as the criteria.

 

[Spreadsheet Solutions]

Debra, Tom;

Thanks for your replies.
Testing this made it clear that the formula in the criteria range gives the
outcome for the first row in the database.
That outcome might return an error.