XL5: Computed Criteria with External Reference Doesn't Work

• 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

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.

REFERENCES

For more information about Setting Up A Criteria Range, choose the Search button in Help and type:

   computed criteria