Formula with locked absolute reference

To create a formula with a "locked" absolute reference – a reference that won't be changed during copy or paste, or when rows and columns are inserted or deleted in a worksheet – you can use the INDIRECT function. In the example shown, the formula in E5 is:

=INDIRECT("B5")

This approach can be useful when a worksheet is routinely edited in a way that would break traditional cell references. This reference to cell B5 won't change during copy/paste/cut operations, or when columns or rows are inserted/deleted in a worksheet.

Explanation

In this example, the goal is to create an "locked" reference that won't change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation.

The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not susceptible to changes, like a normal cell reference. It will continue to evaluate to the same location regardless of changes to the worksheet. For example, this formula:

=INDIRECT("A1")

will continue to return a reference to cell A1 even if row 1, or column A, are deleted.

Examples

A reference created with INDIRECT can be used just like a regular cell reference in other formulas. In the worksheet shown above, the formulas in E5:E9 use INDIRECT to create a locked reference that won't change:

=INDIRECT("B5")
=SUM(INDIRECT("B5:B16"))
=MIN(INDIRECT("B5:B16"))
=MAX(INDIRECT("B5:B16"))
=COUNT(INDIRECT("B5:B16"))

This approach can be useful when a worksheet is routinely edited in a way that would break traditional cell references.

Sheet names

Formulas with sheet names must follow standard rules. Sheets names without spaces or punctuation need no extra handling:

=INDIRECT("Sheet1!A1")

Sheet names with space or punctuation need to be enclosed in single quotes ('):

=INDIRECT("'Sheet 1'!A1") // note single quotes

Note: if you *rename* a sheet name used in INDIRECT, the reference will break. This happens because the reference is entered as text and therefore is not automatically updated like a normal reference.

Different from absolute and relative references

Using INDIRECT is different from standard absolute, relative, and mixed references. The $ syntax is designed to allow "intelligent" copying and pasting of formulas, so that references that need to change will change, while references that shouldn't change, will not change. Using INDIRECT with text references stops all changes to the reference, even when columns/rows are inserted or deleted, or when a sheet is renamed.

Note: INDIRECT is a "volatile function" function, and can cause slow performance in large or complicated workbooks.