Using Computed Criteria on External Databases

ID: Q70100

The information in this article applies to:

SUMMARY

Using computed criteria in Microsoft Excel between two separate sheets performs differently in Microsoft Excel version 2.1 than it does in later versions. If you are setting up computed criteria on a separate sheet from the database, you need to use field names instead of cell references. This was not the case in earlier versions.


MORE INFORMATION

Steps to Reproduce Problem

  1. Start Excel and then open another new sheet. Now you should have two blank sheets open, Sheet1 and Sheet2.


  2. On Sheet1, type the following data: 

    
      A1: Name   B1: Age
      A2: Andy   B2: 23
      A3: Joe    B3: 34
      A4: Bill   B4: 12


  3. Select cells A1:B4 and choose Set Database from the Data menu.


  4. On Sheet2, choose Define Name from the Formula menu and type the name Database in the Refers To: box. Type in Sheet1!database.


  5. Remain on Sheet2 and type the following information: 

    
      A1: 34  B1:
      A2:     B2:  =Sheet1!Age<$A$1

    Note: The formula in cell B2 will give you a #NAME? error if Age hasn't been defined, but that is OK. Also, make sure that your reference to a cell outside of the database is absolute (that is, $A$1 not A1).


  6. Select cells B1:B2 and choose Set Criteria from the Data menu.


  7. Copy the field names Name and Age from Sheet1, and paste them into cells A4:B4 on Sheet2. While A4:B4 are still selected on Sheet2, choose Extract from the Data menu.


Notice that the records for Andy and Bill are extracted, which is correct. This works properly for version 2.1 and 3.0. However, if you substitute a cell reference for the word Age in the formula in cell B2 of Sheet2, your extract will be correct in version 2.1 but will be incorrect in later versions.

Example

If the formula in cell B2 of Sheet2 reads

=Sheet1!B2<$A$1

where B2 refers to the first data cell in the field Age, the extract for Microsoft Excel 2.1 will still correctly extract the records of Andy and Bill; however, in later versions, all the records in the database will be extracted.

In the case of versions 3.0 and 4.0, if your computed criteria formula results in TRUE, all records of the database are extracted and if the formula results in FALSE, none of the records are extracted. Microsoft Excel evaluates the formula once for each record and selects the record if the formula's result is TRUE or a number other than zero. In Microsoft Excel versions 3.0 and 4.0, the relative reference doesn't change because the formula is tested for each record; thus, it is necessary to use the field name.

Note: If you are setting up your database, criteria, and extract all on a single sheet in version 3.0 or 4.0, it is not necessary to use a field name instead of a reference.

For more information on computed criteria, query on the following keywords: 


   computed and criteria and references


REFERENCES

"User's Guide," version 4.0, pages 332-334
"User's Guide," version 3.0 for Windows, pages 374-378
"Microsoft Excel for Windows Reference Guide," version 2.1, pages 180-181

Additional query words: 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 21, 1999