XL97: Defined Name Formula Is Not Updated After Sort (186386)

CAUSE

This problem may occur when the following conditions are true:

You sort a range that contains multiple occurrences of the same formula. -and-
The formula you use is a defined name that refers to a relative or mixed cell reference (for example "=Sheet1!$A1"). -and-
You press CTRL+ENTER, use the fill handle, or use a copy-and-paste operation to type a formula into multiple cells. -and-
You sort the range. -and-
You change a value in a cell that a formula references.

RESOLUTION

To prevent this problem, obtain and install Microsoft Office 97, Service Release 2 (SR-2).

For additional information about SR-2, please see the following article in the Microsoft Knowledge Base:

151261 OFF97: How to Obtain and Install MS Office 97 SR-2

MORE INFORMATION

Example

To see an example of the problem that is described in this article, follow these steps:

Start Microsoft Excel 97 and create a new workbook.
Select $A$2 in Sheet1.
On the Insert menu, point to Name, and then click Define on the menu that appears. In the Names In Workbook box, type Test and in the Refers To box, type =Sheet1!$A2. Click OK.

Type the following into Sheet1:

       A1: Number   B1: Formula
       A2: 1        B2:
       A3: 1        B3:
       A4: 0        B4:

Select B2:B4.
Type =Test and press CTRL+ENTER to type the formula into B2:B4.
Select A4.
On the Data menu, click Sort. Select Number in the Sort By list and click to select Ascending; then, click OK.
On the Data menu, click Sort. Select Number in the Sort By list and click to select Descending; then, click OK.
Type 4 in A4.

Note that the formula in B4 is not updated. The value in B4 should be 4.