< Go Back

Excel INDIRECT function

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.

Syntax

=INDIRECT (ref_text, [a1])

Argument 

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.

Example

If left blank, the function will take a default logical value TRUE = A1 style.

Figure 1.Different Worksheet reference with INDIRECT function

Explanation

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar