Microsoft KB Archive/192810

= XL97: Formulas Not Recalculated After Moving Worksheet =

Article ID: 192810

Article Last Modified on 10/10/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q192810





SYMPTOMS
When you move a worksheet in your workbook, some formulas may not be recalculated automatically.



CAUSE
This problem occurs when the following conditions are true:
 * A worksheet contains a formula that links to another cell.

-and-
 * The cell that the formula links to contains a formula that uses a 3-D reference to several other worksheets.

-and-
 * You move a worksheet that is included in the 3-D reference outside of the range of worksheets included in the 3-D reference.



WORKAROUND
To work around the problem, press CTRL+ALT+F9 to recalculate the entire workbook.



STATUS
Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows.



Example of the Problem
To see an example of the problem, follow these steps:  Save and close any open workbooks and create a new workbook. If the default workbook does not contain at least five worksheets, insert a worksheet into your workbook. To do so, click Worksheet on the Insert menu.

Repeat step 2 until your workbook contains five worksheets.  Rearrange your worksheets so that they are in the following order:      Sheet1 Sheet2 Sheet3 Sheet4 Sheet5  Activate Sheet3. Type 1 in cell A1, and then press ENTER.</li> Activate Sheet4. Type 1 in cell A1, and then press ENTER.</li> Activate Sheet5. Type 1 in cell A1, and then press ENTER.</li>  Activate Sheet2. Type the following formula in cell A1, and then press ENTER: <pre class="fixed_text">  =SUM(Sheet3:Sheet5!A1) The formula returns a value of 3. </li>  Activate Sheet1. Type the following formula in cell A1, and then press ENTER: <pre class="fixed_text">  =Sheet2!A1 The formula returns a value of 3. </li> Activate Sheet4. On the Edit menu, click Move or Copy Sheet. In the Before Sheet list box, click (move to end), and then click OK.</li></ol>

The formula in cell A1 of Sheet2 correctly returns a value of 2. However, the formula in cell A1 of Sheet1 incorrectly returns a value of 3.

<div class="references_section">