XL: External Link Not Updated When Source Cell Moved

ID: Q149660

The information in this article applies to:

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.

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" (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.

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.00 5.00c 7.00 8.00 97 98 XL98 XL97 XL7 XL5 link insert reference defined name

Keywords          : xlformula 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: January 7, 1999