Microsoft KB Archive/98651

= Incorrect Records Filtered or Extracted with TFE or AEE =

Article ID: 98651

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q98651





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

-

 On the Tools menu, click Options (Preferences if you are using Microsoft Excel for the Macintosh.) Click the Transition tab, and under Sheet Options, clear the Transition Formula Evaluation check box. Click OK.

Microsoft Excel Version 4.0

---

 On the Options menu, click Calculation.</li> In the Calculation Options dialog box, clear the Alternate Expression Evaluation check box. Click OK.</li></ol> </li></ul>

<div class="moreinformation_section">

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.

<div class="references_section">