Microsoft KB Archive/214243

= XL2000: External Link Is Not Updated When Source Cell Is Moved =

Article ID: 214243

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214243





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



CAUSE
Your linking formula may not be updated 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 that of versions of Microsoft Excel prior to Excel 5.0.



RESOLUTION
To make sure your linking formulas are updated correctly when the source workbook changes, do either of the following:  Verify that 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 to cell references. For example, if your linking formula is currently

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

you will 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" (without quotation marks). If this is done, you will want to change your linking formula to the following:

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





MORE INFORMATION
To create a defined name, follow these 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, follow these steps:
 * 1) In the linked workbook, select the cell with the link.
 * 2) On the Formula bar, drag the pointer to select the cell reference, for example, $A$5.
 * 3) Type the defined name that corresponds to that cell, and then press ENTER.

Additional query words: link insert reference defined name XL2000

Keywords: kbprb KB214243

-

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

© Microsoft Corporation. All rights reserved.