Microsoft KB Archive/70475

{| = Using INDIRECT to Prevent the Updating of Linked References =
 * width="100%"|

Last reviewed: November 7, 1994

Article ID: Q70475 The information in this article applies to:
 * Microsoft Excel for Windows, version 3.0, 4.0

SUMMARY
The INDIRECT function can be used to prevent Microsoft Excel from changing references on a dependent worksheet when columns or rows are added or deleted from the source worksheet. This may be desired if you bring version 2.x files into version 3.0 or 4.0 and want the worksheet to behave in the same manner as in Microsoft Excel 2.x.

MORE INFORMATION
In earlier versions of Microsoft Excel, linked references on the dependent worksheet were static and would not change regardless of what changes were made to the source worksheet. To work around this in Microsoft Excel version 2.x, you need to use defined names in your link. In Microsoft Excel version 3.0, the linking feature has been enhanced to be more dynamic. Microsoft Excel version 3.0 assumes that if you link to a particular cell on a worksheet, you will want to maintain this relationship even if the linked cell has been affected by the addition or deletion of cells. Microsoft Excel version 3.0 does this by using a table of named ranges on the source document.

To illustrate this, assume the following formula is located on SHEET2.XLS:

A1: =SHEET1.XLS!$A$1 If row 1 on SHEET1.XLS is highlighted and deleted, the formula on SHEET2.XLS remains unchanged in Microsoft Excel version 2.x. In Microsoft Excel version 3.0, the same formula reads: A1: =SHEET1.XLS!#REF! Because Microsoft Excel version 3.0 uses named ranges rather than cell references, after the deletion, the cell no longer exists. This results in a #REF error. To bypass this new feature of Microsoft Excel version 3.0, the following formula can be substituted:

A1: =INDIRECT(&quot;sheet1.xls!$a$1&quot;) Because the reference is a text string rather than a reference, it will not update when the row is deleted. Rather, it will return the value that is now in cell A1 on SHEET1.XLS.