Using INDIRECT Maintains an Absolute Range in a Defined Name

• Microsoft Excel for Windows, versions 3.0, 4.0, 5.0
• Microsoft Excel for OS/2 version 3.0

SUMMARY

When you add or delete a row or column within a named range, Microsoft Excel versions above update the name's reference to include the change. This is inconsistent with earlier versions of Microsoft Excel and is the result of a new dynamic method of linking with Microsoft Excel version 3.0 and later.

To maintain an absolute reference to a range, use the INDIRECT function when defining the name. You can still use this name normally, and it acts as it did in earlier versions of Microsoft Excel. When entering the argument to the function, give the range in the form of text. This text is not updated when rows or columns are added or deleted or parts of the range are moved elsewhere in the sheet.

NOTE: Names defined in this way will not be listed when you Choose the Goto option from the Formula menu. To Goto such a name, type the name in the Reference field.

Example

1. Select cells A1 through A3 in a worksheet.

2. From the Formula menu, choose Define Name.

3. In the Refers To box, type "=INDIRECT("$A$1:$A$3")" (without the surrounding quotation marks).

4. In the Name box, type "Test" (without the quotation marks).

5. Choose OK.

REFERENCES

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