Microsoft KB Archive/61290

{|
 * width="100%"|

Excel: Reference Requirements of Computed Criteria

 * }

-

The information in this article applies to:


 * Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0

-

SUMMARY
When computed criteria is used in Microsoft Excel, the references in the formula must contain both of the following:


 * A relative reference to the first record in the database
 * An absolute reference to any other cells on the worksheet to be included in the formula

MORE INFORMATION
For example, the following worksheet has a defined database of cells A1:B9:

  A1:   Date   B1: Amount C1:     D1: 1/1/90 A2: 12/1/89 B2:  100   C2:     D2: A3: 12/15/89 B3: 150   C3:     D3: A4: 12/19/89 B4: 125   C4:     D4: A5: 12/29/89 B5: 155   C5:     D5: A6:  1/5/90 B6:  175   C6:     D6: A7: 1/12/90 B7:  150   C7:     D7: A8: 1/20/90 B8:  200   C8:     D8: A9: 1/27/90 B9:  185   C9:     D9:

To find all records in the database with dates later than January 1, 1990, the following computed criteria can be used

  =A2>DATE(90,1,1)

where A2 is a relative reference to the Date field in the first record of the database.

Furthermore, to find all records in a database with dates later than the date value of cell D1, the following computed criteria can be used

  =A2>$D$1

where A2 is a relative reference to the Date field in the first record of the database and $D$1 is an absolute reference to the cell that contains the comparison date value.