XL98: Formulas Not Recalculated After Moving Worksheet (192811)


The information in this article applies to:

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

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 COMMAND+SHIFT+F9 to recalculate the entire workbook.

STATUS

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

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 this, 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 RETURN.
  5. Activate Sheet4. Type 1 in cell A1, and then press RETURN.
  6. Activate Sheet5. Type 1 in cell A1, and then press RETURN.
  7. Activate Sheet2. Type the following formula in cell A1, and then press RETURN:

    =SUM(Sheet3:Sheet5!A1)

    The formula returns a value of 3.
  8. Activate Sheet1. Type the following formula in cell A1, and then press RETURN:

    =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). 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, please see the following article in the Microsoft Knowledge Base:

179216 OFF98: How to Use the Microsoft Office Installer Program

Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbbug KB192811
©2002 Microsoft Corporation. All rights reserved.