Microsoft KB Archive/166759

= XL97: Advanced Filter Doesn't Return Any Data =

Article ID: 166759

Article Last Modified on 10/22/2000

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q166759



SYMPTOMS
When you filter data using Advanced Filter (point to Filter on the Data menu), Microsoft Excel fails to return data to the worksheet.



CAUSE
This problem occurs when the following conditions are true:
 * The database is on a different worksheet than the criteria. -and-


 * The criteria compares one or more records.



WORKAROUND
To work around this problem use either of the following methods.

Method 1
Move the Criteria and Copy To (Extract) ranges to the worksheet that contains the database.

Method 2
Instead of using a comparison function, use another operation in the Criteria range.

Consider the following example formula in the Criteria range:

A1: Criteria

A2: =Month(Sheet1!A2)=1

When you use the formula that is in cell A2, it searches the database Date field and finds each record that matches the month of January (the first month).

As an alternative, use the following criteria in cells A1:B2:   A1: Date         B1: Date A2: >=1/1/96    B2: <=1/31/96 NOTE: By typing both conditions on the same row, you create an AND condition. For an explanation of the different conditions, see the "More Information" section.



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



MORE INFORMATION
Criteria for an advanced filter can include multiple conditions applied in a single column, multiple criteria applied to multiple columns, and conditions created as the result of a formula.

Multiple Conditions in a Single Column
If there is more than one condition in a single criteria column, you create a logical OR condition because you extract all records that apply to any of the conditions.

The following example searches the Region field and extracts all records that match South or West.

A1: Region

A2: South

A3: West

Two or More Columns
When the criteria includes more than one column with the same field name, and the conditions are on the same row, you create a logical AND condition. This condition implies that you want to extract all records that match each condition for that field.

The following example searches the Date field and extracts all records with dates in January 1996.   A1: Date         B1: Date A2: >=1/1/96    B2: <=1/31/96 To find records that meet one of many conditions, type the condition in separate rows.

The following example searches the Date field and extracts all records with dates that are 1/1/96 or 1/31/96.   A1: Date         B1: Date A2: 1/1/96      B2: A3:             B3: 1/31/96

Condition as a Result of a Formula
Conditions that are the result of a formula are also called computed criteria. You can use any formula that returns a result of True or False as criteria. This is useful when you need to be more exclusive in the records you are attempting to match.

The following example extracts all records in which column A equals January, column B equals North, and column C is greater than 100:

A1: criteria

A2: =AND(Month(A1)=1,B1="North",C1>100)

