XL97: Defined Name Formula Is Not Updated After Sort

When you use the Sort command on the Data menu, some formulas on your worksheet may not be automatically updated.

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.

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:

Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows. This problem was corrected in Microsoft Excel 97 for Windows, Service Release 2 (SR-2).

Example

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

1. Start Microsoft Excel 97 and create a new workbook.
2. Select $A$2 in Sheet1.
3. 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.
4. Type the following into Sheet1:

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

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

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