KB186687 - XL98: Defined Name Formula Is Not Updated After Sort

CAUSE

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 CONTROL+RETURN, use the fill handle, or copy and then paste to insert a formula in multiple cells. -and-

You sort the range. -and-

You change a value in a cell that a formula references.

MORE INFORMATION

To see an example of this problem, follow these steps:

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

Type the following in Sheet1:

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

Select B2:B4.
Type =Test and press CONTROL+RETURN to enter the formula in B2:B4.
Select A4.
On the Data menu, click Sort. Click Number in the Sort By list and click Ascending. Then, click OK.
On the Data menu, click Sort. Click Number in the Sort By list and click Descending. Then, click OK.
Type 4 in A4.

The formula in B4 is not recalculated. The value in B4 should be 4.