Formula with locked reference

Formula with locked reference

Generic formula 

=INDIRECT("A1")

Related formulas 

Dynamic lookup table with INDIRECT

Sum top n values

Lookup with variable sheet name

Explanation

to create a formula with a "locked" reference – a reference that won't be adjusted during copy or paste, or when rows and columns are changed in a worksheet – you can use the INDIRECT function.

How this formula works

The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not adjusted like a normal reference. It continues to evaluate with the same result no matter what changes.

For example, this formula:

=INDIRECT("A1")

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

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 update while references that shouldn't change, won't. Indirect permanently stops all changes to the reference, even when columns/rows are inserted or deleted.

Note: indirect is a "volatile" function, which means it evaluates whenever any change is made to a worksheet. Be careful when using INDIRECT in large and complicated workbooks, as it can cause slow performance.

Formula with locked reference

Generic formula 

=INDIRECT("A1")

Related formulas 

Dynamic lookup table with INDIRECT

Sum top n values

Lookup with variable sheet name

Explanation

to create a formula with a "locked" reference – a reference that won't be adjusted during copy or paste, or when rows and columns are changed in a worksheet – you can use the INDIRECT function.

How this formula works

The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not adjusted like a normal reference. It continues to evaluate with the same result no matter what changes.

For example, this formula:

=INDIRECT("A1")

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

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 update while references that shouldn't change, won't. Indirect permanently stops all changes to the reference, even when columns/rows are inserted or deleted.

Note: indirect is a "volatile" function, which means it evaluates whenever any change is made to a worksheet. Be careful when using INDIRECT in large and complicated workbooks, as it can cause slow performance.