XL2000: Incorrect Records Filtered or Extracted with Transition Formula Evaluation (214086)

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) option is selected in Microsoft Excel. 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 2000, when you use the Advanced Filter command 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.

WORKAROUND

To work around this behavior, use either of the following methods:

To receive the correct result when you are using both a computed criteria and Transition Formula Evaluation, 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-
Turn off the Transition Formula Evaluation option by using the following steps:
1. On the Tools menu, click Options.
2. Click the Transition tab. Under Sheet Options, click to clear the Transition formula evaluation check box. Click OK.

MORE INFORMATION

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