Excel INDIRECT Function
The Excel INDIRECT function returns a valid reference from a given text string. INDIRECT is useful when you want to convert a text value into a valid cell reference.
- ref_text - A reference supplied as text.
- a1 - [optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.
The INDIRECT function returns a valid reference from a given text string. INDIRECT is useful when you want to convert a text value into a valid cell reference. For example, to create a reference to cell A1 from text, you can use INDIRECT like this:
=INDIRECT("A1") // returns reference to A1
The purpose of INDIRECT may at first seem baffling (i.e. why use text when you can just provide a proper reference?) but there are many situations where the ability to create a reference from text is useful, including:
- A formula that needs a variable sheet name
- A fixed reference that will not change even when rows or columns are deleted
- Creating numeric arrays with the ROW function in complex formulas
Note: INDIRECT is a volatile function and can cause performance problems in large or complex worksheets.
Variable worksheet name
In the example shown above, INDIRECT is set up to use a variable sheet name like this:
=INDIRECT(B6&"!A1") // sheet name in B6 is variable
The formula in B6, copied down, returns the value in cell A1 in the first 5 sheets using the sheet names entered in column B. In other words, when a different sheet name is entered in column B, a new result is returned. With the same approach, you could allow a user to select a sheet name with a dropdown list, then construct a reference to the selected sheet with INDIRECT.
Note: sheet names that contain punctuation or space must be enclosed in single quotes ('), as explained in this example. This is not specific to the INDIRECT function; the same is true in all formulas.
The reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. For example, the formula below will always refer to the the first 100 rows of column A, even if rows in that range are deleted or inserted:
=INDIRECT("A1:A100") // will not change
Generate numeric array
One use case is explained in this formula, which sums the bottom n values in a range.
You may also run into this idea in more complex formulas that need to assemble a numeric array "on-the-fly". One example is this formula, designed to strip numeric characters from a string.
- References created by INDIRECT are evaluated in real time and the content of the reference is displayed.
- When ref_text is an external reference to another workbook, the workbook must be open.
- a1 is optional. When omitted, a1 is TRUE = A1 style reference.
- When a1 is set to FALSE, INDIRECT will created a an R1C1-style reference.
- INDIRECT is a volatile function, and can cause performance issues.