Microsoft Knowledge Base

XL: Change Source Causes Incorrect Relative Reference
Last reviewed: November 8, 1996
Article ID: Q119261
The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS

In Microsoft Excel, if you define a name that contains both a reference to another workbook, and a relative reference, the relative reference changes incorrectly when you change the source of the link using the Links dialog box.

CAUSE

This behavior occurs when a cell other than A1 is selected on the worksheet when you use the Change Source button in the Links dialog box. For example, if you select the cell C4, and create a name that refers to the following 

   =INDEX([SUPPORT.XLS]Sheet1!$A$1:$C$9,Sheet1!$B4,Sheet1!C$3)
these references change to the following, if the cell C4 is selected when you use the Change Source button: 

   =INDEX([SUPPORT.XLS]Sheet1!$A$1:$C$9,Sheet1!$B1,Sheet1!A$3)
Note that this behavior occurs even if you select the same source document in the Change Links dialog box.

WORKAROUND

To avoid an incorrect relative reference in a defined name that contains a reference to another workbook, select cell A1 on the worksheet before you use the Change Source button in the Links dialog box.

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.

REFERENCES

For more information about Changing The Reference To A Source Area, choose the Search button in Help and type: 

   links, to source data, changing
For more information about Naming A Cell, Range, Or Formula, choose the Search button in Help and type: 

   names, creating

KBCategory: kbprb
KBSubcategory:
Additional reference words: 5.00 5.00c 7.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: November 8, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.