Microsoft KB Archive/149660

= External Link Not Updated When Source Cell Moved =

Article ID: 149660

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q149660



SYMPTOMS
If you have a formula that links to another workbook (source workbook), your linking formula may not be updated when the source workbook is changed.



CAUSE
Your linking formula may not update if both of the following are true:
 * You insert a row or column, or you delete a row or column in the source workbook. -and-


 * The linked workbook is not open when you make these changes to the source workbook.

NOTE: This behavior is different from Microsoft Excel version 4.0.



RESOLUTION
To make sure your linking formulas update correctly when the source workbook changes, do either of the following:  Make sure the dependent workbook (with the linking formulas) is open when you make changes to the source workbook. -or-

  Use defined names in the source workbook and change your linking formulas so they refer to these defined names and not cell references.

For example, if your linking formula is currently:    Microsoft Windows -

='C:\Directory\[Source.xls]Sheet1'!$A$5

Macintosh -

='hd:direcory:[Source]Sheet1'!$A$5 then you would want to give cell A5 in the source workbook a defined name. For this example, you can define cell A5 in the source workbook as mycell. If this is done, you would want to change your linking formula to:    Microsoft Windows -

='C:\Directory\Source.xls'!Mycell

Macintosh -

='hd:direcory:source'!mycell 



MORE INFORMATION
To create a defined name, use the following steps:
 * 1) Select a cell or a range of cells.
 * 2) On the Insert Menu point to Name and then click Define.
 * 3) In the Define Name dialog box, type a name for the cell or range, and then click OK.

To change the formula for the link, do the following:


 * 1) Go to the linked workbook and select the cell with the link.
 * 2) In the formula bar, drag the mouse to select the cell reference, including the '$' signs. For example $A$5.
 * 3) Type the defined name that corresponds to that cell, and then press ENTER.

Additional query words: 5.00c 8.00 97 98 XL98 XL97 XL7 XL5 link insert reference defined name XL

Keywords: kbprb KB149660

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.