XL98: Defined Name Formula Is Not Updated After Sort (186687)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q186687

SYMPTOMS

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

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

WORKAROUND

To work around this problem, press COMMAND+SHIFT+F9. This causes Microsoft Excel to recalculate values in all open workbooks.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

Example

To see an example of this problem, follow these steps:
  1. Start Microsoft Excel 98 and create a new workbook.
  2. Select $A$2 on Sheet1.
  3. 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.
  4. Type the following in Sheet1: 
           A1: Number   B1: Formula
       A2: 1        B2:
       A3: 1        B3:
       A4: 0        B4:
  5. Select B2:B4.
  6. Type =Test and press CONTROL+RETURN to enter the formula in B2:B4.
  7. Select A4.
  8. On the Data menu, click Sort. Click Number in the Sort By list and click Ascending. Then, click OK.
  9. On the Data menu, click Sort. Click Number in the Sort By list and click Descending. Then, click OK.
  10. Type 4 in A4.
The formula in B4 is not recalculated. The value in B4 should be 4.
Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbbug KB186687
©2002 Microsoft Corporation. All rights reserved.