XL: Advanced Filter Does Not Return All Matching Records

ID: Q175897

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

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:

   ARTICLE-ID: Q161881
   TITLE     : XL97: How to Use Natural Language Formulas

REFERENCES

For more information about the Advanced Filter command, click the Index tab in Microsoft Excel Help, type the following text

   advanced filters

and then double-click the selected text to go to the "Filter a list by using advanced criteria" topic.

Additional query words: nlf XL98

Keywords          : xlformula xllist 
Version           : WINDOWS:97; MACINTOSH:98
Platform          : MACINTOSH WINDOWS
Issue type        : kbbug

Last Reviewed: January 8, 1999