Microsoft KB Archive/214086

= XL2000: Incorrect Records Filtered or Extracted with Transition Formula Evaluation =

Article ID: 214086

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214086





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:
 * On the Tools menu, click Options.
 * 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.

Additional query words: XL2000 TFE

Keywords: kbprb KB214086

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.