# INDIRECT

## 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.

### 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.

### 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.

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.