Microsoft KB Archive/186687

= XL98: Defined Name Formula Is Not Updated After Sort =

Article ID: 186687

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



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.



Example
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: </li> Select B2:B4.</li> Type =Test and press CONTROL+RETURN to enter the formula in B2:B4.</li> Select A4.</li> On the Data menu, click Sort. Click Number in the Sort By list and click Ascending. Then, click OK.</li> On the Data menu, click Sort. Click Number in the Sort By list and click Descending. Then, click OK.</li> Type 4 in A4.</li></ol>

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

Additional query words: sorting recalculate XL98 calc recalc calculate

Keywords: kbbug kbpending KB186687

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.