XL97: Defined Name Formula Is Not Updated After Sort

ID: Q186386

The information in this article applies to:

Microsoft Excel 97 for Windows

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:

   ARTICLE-ID: Q151261
   TITLE     : 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" (without the
    quotation marks) and in the Refers To box, type "=Sheet1!$A2" (without
    the quotation marks). 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" (without the quotation marks) 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          : kbdta xlformula xllist 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbfix

Last Reviewed: November 6, 1998