XL2000: Using Criteria to Perform an Advanced Filter Displays Unexpected or No Results (214363)


The information in this article applies to:

  • Microsoft Excel 2000
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.

REFERENCES

For more information about advanced filters, click Microsoft Excel Help on the Help menu, type filter a list by using advanced criteria in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about using comparison criteria, click Microsoft Excel Help on the Help menu, type types of comparison criteria in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about natural language formulas, click the article number below to view the article in the Microsoft Knowledge Base:

279412 XL2000: How to Use Natural Language Formulas

Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbbug kbpending KB214363
©2003 Microsoft Corporation. All rights reserved.