XL: Advanced Filter Does Not Return All Matching Records

Last reviewed: February 5, 1998
Article 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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
Solution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 5, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.