Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 214363 - Last Review: September 25, 2003 - Revision: 3.0
XL2000: Using Criteria to Perform an Advanced Filter Displays Unexpected or No Results
This article was previously published under Q214363
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.
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
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
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
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:
- On the Tools menu, click Options.
- On the Calculation tab, click to select the Accept labels in formulas check box.
- Click OK.
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=279412/EN-US/
)
XL2000: How to Use Natural Language Formulas
APPLIES TO
- Microsoft Excel 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate