INDIRECT
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:
- The ADDRESS function with five arguments creates a full address in text format.
- If cell A1 contains “Report”, the ADDRESS function will return the text: ‘Report’!$C$2.
- The INDIRECT function converts this text into a working reference.
- 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.