The INDIRECT function is to return the reference you specified by a text string. INDIRECT function can change the reference to a cell without changing the formula.
= INDIRECT(ref_text, [a1])
- Ref_text is required, the reference.
- A1 is optional; a logical number specifies A1 style or R1C1 style.
– When A1 is true or omitted, ref_text is in A1 style;
– When A1 is false, ref_text is in R1C1 style.
When ref_text is not a valid cell reference, INDIRECT returns the #REF! error.
= INDIRECT(A2) Value of reference in cell A2, which refers to B2 and returns 85.
= INDIRECT(A3) Value of reference in cell A3, which refers to A2 and returns B2.
= INDIRECT(A4) Value of reference in cell A4, which is a word By, an invalid reference and returns #REF! ERROR.
= INDIRECT(A5) Value of reference in cell A5, which refers to B7, an empty cell, and returns 0.