XL5: GP Fault Using Shared Formula as Computed Criteria

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you filter a list using computed criteria, and the following conditions are true

• The computed criteria contains a shared formula

  -and-

• The criteria is on a different worksheet from your list

  -and-

• The formula contains a defined name

CAUSE

This error occurs when you change the value in the cell that the defined name refers to, when the defined name is used as a computed criteria on a worksheet other than the worksheet that contains the list. This problem occurs when you use the Advanced Filter feature to filter the list.

Note that if you use one of the database functions such as DCOUNT() under the above conditions, you may either receive a GP fault, or the value in the computed criteria may not change when you change the value in the cell that the defined name refers to.

WORKAROUNDS

To avoid receiving a GP fault when you use a computed criteria on one sheet, with a list on another worksheet, do any of the following:

• Do not use a cell that contains a shared formula as the computed criteria.

  -or-

• Refer to the cell that contains the computed criteria value by using the cell reference, such as =A1, instead of a defined name.

  -or-

• Use a computed criteria that is located on the same sheet as the list.

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 for Windows version 5.0c.

REFERENCES

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

    computed criteria