Microsoft KB Archive/120684

{|
 * width="100%"|

XL5: Computed Criteria with External Reference Doesn't Work

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 5.0

-

SYMPTOMS
In Microsoft Excel, if you use the Advanced Filter to filter records in a list, and you use an external reference in your criteria range, either none of the records in your list are filtered, or all of the records in the list are filtered.

CAUSE
This problem occurs even if your list and criteria range are contained on the same sheet. This behavior occurs when you use an external reference in the criteria range such as the following

"=Sheet1!A2=1" -or-

"=Sheet1!Age=1" -or-

"=BOOK1.XLS!Age=1" where Age is a column heading in your list.

WORKAROUNDS
To avoid this problem, and to ensure that the computed criteria is used correctly to filter your list, use the cell reference or field reference without the sheet name as in the following examples:

"=A2=1" -or-

"=Age=1"

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0c for Windows.