INDIRECT

Returns a reference indicated by a text value
Create a cell reference using a text string in Excel

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:

Returns value from a data as per given offset range in Excel

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.

Read More »
Return a cell value as per row‐and‐column intersection in Excel

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.

Read More »
Return address of a cell in Excel

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.

Read More »