Exceljet

Quick, clean, and to the point

Formula with locked absolute reference

Excel formula: Formula with locked absolute reference
Generic formula 
=INDIRECT("A1")
Summary 

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.