The INDIRECT function returns the contents of the cell specified by the reference.
This function can be useful for dynamic cell referencing.
Syntax
INDIRECT(link_label, [reference_style])
Argument |
Description |
Permitted values |
|---|---|---|
link_label |
Text string |
"A1", "B2:C5", "Sheet1!A1" |
[reference_style] |
(optional) Style of reference to be returned: A1 (TRUE) or R1C1 (FALSE). By default, TRUE |
TRUE or FALSE |
Examples of use
Retrieving the value from cell A1
=INDIRECT("A1")
If cell A1 contains the value 10, the result will be 10.
Retrieving the sum of values from the range
=SUM(INDIRECT("B2:C5"))
This formula sums the values in the range B2:C5.
Creating a formula for a dynamic reference to another sheet using the ADDRESS and INDIRECT functions
=INDIRECT(ADDRESS(2, 3, 1, TRUE, A1))
Analysis of arguments:
–2: Row number (row 2)
–3: Column number (column C)
–1: Reference type (1 or 4 for absolute/relative reference)
–TRUE: A1 style (mandatory condition for correct operation with INDIRECT function)
–A1: Cell with sheet name
Formula calculation order:
1.The ADDRESS function with five arguments creates a full address in text format.
2.If cell A1 contains “Report”, the ADDRESS function will return the text: ‘Report’!$C$2.
3.The INDIRECT function converts this text into a working reference.
4.The formula returns the value from cell C2 of the “Report” sheet.
Notes
If the specified address does not exist, the function will return an error.