Microsoft KB Archive/128153

Microsoft Knowledge Base

MXL5: Cell Reference Following Defined Name Isn't Updated

Last reviewed: September 12, 1996
Article ID: Q128153

The information in this article applies to:

• 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))

if you insert a row above row 20 on the macro sheet, the cell reference in the formula remains A20, even though the contents of cell A20 has moved to cell A21.

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

KBCategory: kbother KBSubcategory: xlmac Additional reference words: 5.00 THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Last reviewed: September 12, 1996 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.