Microsoft KB Archive/175897

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

Article ID: 175897

Article Last Modified on 8/15/2003

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q175897





SYMPTOMS
When you use the Advanced Filter command (point to Filter on the Data menu), 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.



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

Example
To use the criteria that is listed in the table in this section, enter 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 and two examples of criteria that you can use to work around the problem.   Computed criteria      Computed criteria with labels           without labels         Comparison criteria -

D1: X_Sales           E1: X_Sales            E1: Sales D2: =B2>North Sales   E2: =B2>500            E2: >500



STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.



MORE INFORMATION
Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition introduce 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 Microsoft Excel. However, when you use natural language formulas, you no longer have to create defined names like you do in earlier versions of Microsoft Excel.

For additional information about natural language formulas, please see the following article in the Microsoft Knowledge Base:

161881 XL97: How to Use Natural Language Formulas

