Microsoft KB Archive/119261

= 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.