Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

INDIRECT

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

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:

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 »
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 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 »