The INDIRECT function returns a reference to a range from a given text string. This function can be used to create a reference that won’t change if row or columns are inserted in the worksheet. It can also be used to create a reference from text and numbers in other cells.
=INDIRECT (ref_text, [a1])
ref_text – The cell reference given in a text format.
a1 – setting this parameter is optional. It is a logical value used to indicate which style of reference is used.
If left blank, the function will take a default logical value TRUE = A1 style.
Figure 1.Different Worksheet reference with INDIRECT function
The style of reference, used in the ref_text argument, is given the logical value a1.
In an instance where a1 is TRUE or not set, ref_text assumes the A1 type of cell reference.
In an instance where a1 is FALSE, then ref_text is interpreted as an R1C1 style reference.
A1 reference type is the default and preferable reference style in Excel. In A1 style reference, a row number is preceded by a column number
The R1C1-style reference is the complete opposite of A1 reference type. In this reference type, a row is followed by a column. For example, R3C2 indicates cell B3 which can be found in row 3, column 2 in a spreadsheet. If no number follows the letter, it means the same row or column are being referred to.
INDIRECT function reference does not change even when new cells are added or when old cells are deleted. For example, the formula =INDIRECT(“D1:D100”) is referring to the first 100 rows in column D, even if cells (rows) in that range are changed.