Microsoft KB Archive/214363

= XL2000: Using Criteria to Perform an Advanced Filter Displays Unexpected or No Results =

Article ID: 214363

Article Last Modified on 9/25/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214363





SYMPTOMS
When you use a criteria with the Advanced Filter command, the data that is returned does not match the criteria, or no data is returned.



CAUSE
This problem occurs when you use row or column labels as references in the computed criteria. That is, you refer the value of a cell by using row and column labels.

For example, in the table below you refer to the value 500 as =North Sales. To refer to this as a direct cell reference without using reference labels, you would use =B2.   A1: Region       B1: Sales A2: North       B2: 500 A3: South       B3: 600



WORKAROUND
To work around this problem, use computed criteria without reference labels, or use a comparison criteria instead of computed criteria with labels.

Example
Type the following sample data in a worksheet:   A1: Region       B1: Sales A2: North       B2: 500 A3: South       B3: 600 The following table lists sample computed criteria with label references that illustrate this problem:   Computed criteria with labels -  D1: X_Sales D2: =B2>North Sales The following two examples show criteria that you can use to work around the problem:   Computed criteria without reference labels      Comparison criteria --  E1: X_Sales                    F1: Sales E2: =B2>500                   F2: >500



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
This problem occurs even though Excel uses natural language formulas. When you use a natural language formula, you can refer to a cell range in a table by using row or column labels as the reference name. Natural language formulas use the intersection feature that is available in earlier versions of Excel. When you use natural language formulas, you no longer have to create defined names like you do in earlier versions of Excel.

In Excel 2000, labels in formulas are not used by default. To turn on this option, follow these steps:
 * 1) On the Tools menu, click Options.
 * 2) On the Calculation tab, click to select the Accept labels in formulas check box.
 * 3) Click OK.

