XL97: Advanced Filter Doesn't Return Any Data

ID: Q166759

The information in this article applies to:

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.

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)

REFERENCES

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

advanced filters

and then double-click the selected text to go to the "Examples of advanced filter criteria" topic.

Additional query words: XL97

Keywords : xllist
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb