XL: External Link Not Updated When Source Cell Moved

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

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.

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" (without quotation marks). 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.

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.