Rational
Create a cell reference using a text string.
Description
The Excel INDIRECT function create a cell reference from a given text string. For example: if a sheet name is NUMBER and cell C11 contain the text “Hello” the formula =INDIRECT(B5&”!C11″) returns HELLO, cell B5- NUMBER.
Syntax
= INDIRECT(ref_text, [a1])
Parameters
ref_text – The reference text
a1 [optional] – A logical value (TRUE or FALSE); TRUE – A1 reference style or FALSE – R1C1 reference style
Return Value
Reference in text
Key Notes
In Excel INDIRECT formula, if a worksheet name includes “ “ (space) we must add the single quote “‘” and ampersand “&” in the formula.
Example: If a worksheet name changed from NewYork to New York then the same formula should be changed from =INDIRECT(B6&”!A1″) to =INDIRECT(“‘”&B6&”‘”&”!A1″)
- The function is useful when a user wants to convert a text string to a cell reference. =INDIRECT(A1:A12) will always refer to Row 1 to 12 in A column and does not change even if we add or delete a row or column.
- Excel displays the value containing in the reference but does not adjust the cell reference automatically.
- For a1 [optional] the default argument is TRUE – A1 reference style.
- In case the reference cell is from another workbook, the same workbook must be open simultaneously otherwise returns the #REF! error value.
Keep Reading Similar Functions:
OFFSET
The OFFSET function returns one or multiple data points of a data set by giving a reference point with a specified number of rows and columns to offset.
INDEX
The INDEX function returns a cell value from one or multiple data range at a given row‐and‐column intersection and gets a specific value from a data set.
ADDRESS
The ADDRESS function returns cell address from given row and column numbers of a worksheet. For example, if you pass ADDRESS(5,6) the function returns $F$5.