Using INDIRECT to Prevent the Updating of Linked References

 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("sheet1.xls!$a$1")
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.

REFERENCES

"User's Guide 1," version 4.0, pages 362-373 "User's Guide," version 3.0, pages 309-316

KBCategory: kbusage
KBSubcategory:

Additional reference words: 3.00 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 7, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.