XL: Incorrect Records Filtered or Extracted with TFE or AEE

ID: Q98651

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
Microsoft Excel for the Macintosh, versions 4.0, 5.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SYMPTOMS

When you use computed criteria to filter records in a list, or to extract records from a database, you may receive unexpected results if the Transition Formula Evaluation (TFE) (version 5.0 and later) or Alternate Expression Evaluation (AEE) is enabled (version 4.0). When this option is enabled, all records are extracted regardless of whether they match the specified criteria.

In the following example, the list or database is defined as A1:B4, the computed criteria is the range D1:D2, and the extract range is D5:E5.


   A1: One     B1: Two     D1:
   A2: 1       B2: 10      D2: =A2+B2=22
   A3: 2       B3: 20
   A4: 3       B4: 30
                           D5: One   E5: Two

In Microsoft Excel version 5.0 and later, when you use the Advanced Filter to filter the records in the list above, the only record that should be returned is the record in A3:B3. However, if the Transition Formula Evaluation option is selected, all of the records are displayed.

In Microsoft Excel version 4.0, when you click Extract on the Data menu, the only record that should be returned is the record in A3:B3. However, if the Alternate Expression Evaluation option is selected, all of the records are extracted.

WORKAROUND

To work around this problem, do one of the following:

To receive the correct result when you are using both a computed criteria and TFE or AEE, use the field name of a field from the list or database as a Criteria heading. In the above example, you would change D1 to either One or Two.

-or-

Disable the TFE or AEE option by using the following steps.

Microsoft Excel Version 5.0 and Later
-------------------------------------
1. On the Tools menu, click Options (Preferences if you are using Microsoft Excel for the Macintosh.)
2. Click the Transition tab, and under Sheet Options, clear the Transition Formula Evaluation check box. Click OK.
Microsoft Excel Version 4.0
---------------------------
1. On the Options menu, click Calculation.
2. In the Calculation Options dialog box, clear the Alternate Expression Evaluation check box. Click OK.

MORE INFORMATION

Transition Formula Evaluation and Alternate Expression Evaluation are designed to allow for differences between the way Microsoft Excel and Lotus 1-2-3 evaluate expressions. These options are automatically enabled when you open a Lotus 1-2-3 worksheet in Microsoft Excel.

REFERENCES

"User's Guide 1," version 4.0, pages 57, 332-334

"Switching to Microsoft Excel from Lotus 1-2-3," version 4.0, pages 10-11

Additional query words: 4.00a AEE TFE

Keywords : xllist
Version : WINDOWS:4.0,4.0a,5.0,7.0,97; MACINTOSH:4.0,5.0,98
Platform : MACINTOSH WINDOWS
Issue type : kbprb