XL2000: External Link Is Not Updated When Source Cell Is Moved (214243)

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.

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

To create a defined name, follow these steps:

Select a cell or a range of cells.
On the Insert menu, point to Name, and then click Define.
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:

In the linked workbook, select the cell with the link.
On the Formula bar, drag the pointer to select the cell reference, for example, $A$5.
Type the defined name that corresponds to that cell, and then press ENTER.