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:
will continue to return a reference to cell A1 even if row 1, or column A, are deleted.
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.
Formulas with sheet names must follow standard rules. Sheets names without spaces or punctuation need no extra handling:
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.