XL97: Advanced Filter Doesn't Return Any Data

• Microsoft Excel 97 for Windows

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

As an alternative, use the following criteria in cells A1:B2:

   A1: Date         B1: Date
   A2: >=1/1/96     B2: <=1/31/96

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

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

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)

REFERENCES

For more information about using advanced filters, click the Index tab in Help, type the following text

   advanced filters