XL97: Formulas Not Recalculated After Moving Worksheet (192810)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
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.

MORE INFORMATION

Example of the Problem

To see an example of the problem, follow these steps:
  1. Save and close any open workbooks and create a new workbook.
  2. 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.
  3. Rearrange your worksheets so that they are in the following order:
          Sheet1
      Sheet2
      Sheet3
      Sheet4
      Sheet5
  4. Activate Sheet3. Type 1 in cell A1, and then press ENTER.
  5. Activate Sheet4. Type 1 in cell A1, and then press ENTER.
  6. Activate Sheet5. Type 1 in cell A1, and then press ENTER.
  7. Activate Sheet2. Type the following formula in cell A1, and then press ENTER: 
       =SUM(Sheet3:Sheet5!A1)
    The formula returns a value of 3.
  8. Activate Sheet1. Type the following formula in cell A1, and then press ENTER: 
       =Sheet2!A1
    The formula returns a value of 3.
  9. 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.
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.

REFERENCES

For more information about using 3-D references in formulas, click the Office Assistant, type 3-d formula, click Search, and then click to view "What happens to a 3-D reference when you move, copy, insert, or delete worksheets?."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, click the article number below to view the article in the Microsoft Knowledge Base:

120802 Office: How to Add/Remove a Single Office Program or Component

Modification Type:MinorLast Reviewed:10/10/2005
Keywords:kbbug kbfix kbQFE KB192810
©2004 Microsoft Corporation. All rights reserved.