XL: Incorrect Records Filtered or Extracted with TFE or AEE

• Microsoft Excel for Windows, versions 4.0, 4.0a and 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 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.

WORKAROUNDS

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