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
-------------------------------------
- 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.
- 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
Last Reviewed: April 6, 1999