Microsoft Knowledge Base

• Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS

In Microsoft Excel, when you enter a formula that contains a defined name followed by a cell reference, the reference is not updated when you move that referenced cell on the worksheet.

CAUSE

This behavior occurs only in the 68K version of Microsoft Excel; this problem does not occur in Microsoft Excel for the Power Macintosh.

The problem occurs when a cell reference in a formula is preceded by a name that is not defined.

Additionally, on a macro sheet, this problem occurs if the named reference begins with an exclamation point, such as !Test, even if the name "Test" is defined in the workbook.

For example, if you enter the following formula on a macro sheet

   IF(!Test,GOTO(A20))

On a worksheet, this problem occurs if the name is not defined in the workbook, and does not depend on how you reference the name.

WORKAROUNDS

To work around this problem, use any of the following methods.

Worksheet

On a macro sheet, if a formula on your worksheet contains a defined name followed by a cell reference, make sure that the defined name exists.

Macro Sheet

On a macro sheet, if a formula on the macro sheet contains a defined name followed by a cell reference, make sure that the defined name exists, and that the reference to the name does not begin with an exclamation point. The following examples will not have this problem:

   =IF(Test,GOTO(A20))

   =IF(Sheet2!Test,GOTO(A20))

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0a for the Macintosh.

REFERENCES

"User's Guide," version 5.0, "Moving and Copying Formulas and References," Chapter 10