Excel INDIRECT Function
How to use the INDIRECT Function (WS)
This Excel tutorial explains how to use the Excel INDIRECT function with syntax and examples.
SUMMARY:
The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.
Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
PURPOSE:
Create a reference from text
RETURN VALUE:
The INDIRECT function returns the reference to a cell and thus displays the referenced cell's value.
SYNTAX:
=INDIRECT(ref_text, [a1])
ARGUMENTS:
The INDIRECT function syntax has the following arguments:
- ref_text: Required. A cell reference or a text string (or both), that create a range reference to a cell, range of cells or named range. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.
- If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
(External references are not supported in Excel Web App.) - If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.
- If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
- a1: Optional. It is either a TRUE or FALSE value. A logical value that specifies what type of reference is contained in the cell ref_text.
- If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
- If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.
REMARKS:
Use INDIRECT to create or supply a reference in text form. Indirect is useful when you want to convert a text value into a valid cell reference.
The reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. For example, the formula =INDIRECT("A1:A100") will always refer to the first 100 rows of column A, even if rows in that range are deleted or inserted.
- 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.
Download Example Sample Excel File
indirect-function-example.xlsx