Exceljet

Quick, clean, and to the point

Formula with locked reference

Generic formula 
=INDIRECT("A1")
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 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. 

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. Using INDIRECT stops all changes to the reference, even when columns/rows are inserted or deleted.

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.