Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL2000: External Link Is Not Updated When Source Cell Is Moved

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

View products that this article applies to.

This article was previously published under Q214243

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

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.

↑ Back to the top

Keywords: KB214243, kbprb

↑ Back to the top

Article Info
Article ID : 214243
Revision : 4
Created on : 9/27/2003
Published on : 9/27/2003
Exists online : False
Views : 273