The INDIRECT function returns a reference specified by a text string, evaluates that reference and displays its contents. The function is useful when you want a cell reference in a formula to be variable, without the need to change the formula every time.
ref_text: a text that contains a cell reference.
a1: a logical value that specifies what type of reference is contained in the cell ref_text.
• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. E.g. C12
• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference. E.g R12C3
Cell B1 contains the formula: =INDIRECT(A1)
Cell A1 contains the text: “C6”
Cell C6 contains the value: 135
The INDIRECT function will evaluate the reference as: = C6, which is equal to 135.
If you change the text in cell A1 to “C3” then you will get 198 in B1.
The data shown above is a snapshot from a sheet named “Sheet2″ in a workbook.
Cell B1 contains the formula: =INDIRECT(A1&”!”&”C8″)
Cell A1 contains the text: “Sheet2”
Cell C8 contains the value: 1922
The INDIRECT function will evaluate the reference as: = Sheet2!C8, which is equal to 1922.
If you change the text in cell A1 to “Sheet1” then the reference will be evaluated as: = Sheet1!C8 and you will get whatever value in Cell C8 of sheet1.