Microsoft KB Archive/186386

Knowledge Base

XL97: Defined Name Formula Is Not Updated After Sort

Article ID: 186386

Article Last Modified on 10/22/2000

---

APPLIES TO

• Microsoft Excel 97 Standard Edition

---

This article was previously published under Q186386

SYMPTOMS

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

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

STATUS

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).

MORE INFORMATION

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.

Additional query words: XL97 sorting

Keywords: kbbug kbfix KB186386

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.