Documentation/Calc Functions/INDIRECT

From The Document Foundation Wiki
Jump to: navigation, search

Function name:

INDIRECT

Category:

Spreadsheet

Summary:

Returns the reference specified by a text string. This function can also be used to return the area of a corresponding string.

Syntax:

INDIRECT(Ref[; A1])

Returns:

Return a reference given a string representation of a reference. The cell can contain any value which the function returns.

Arguments:

Ref represents a reference to a cell or an area (in text form) for which to return the contents.

A1 if set to 0, the R1C1 notation is used. If this parameter is absent or set to another value than 0, the A1 notation is used.

Note pin.svg

If you open an Excel spreadsheet that uses indirect addresses calculated from string functions, the sheet addresses will not be translated automatically. For example, the Excel address in INDIRECT("[filename]sheetname!"&B1) is not converted into the Calc address in INDIRECT("filename#sheetname."&B1).


  • If Ref contains an invalid reference/name to any cell then the function returns #REF! error.

Additional details:

  • For interoperability the ADDRESS and INDIRECT functions support an optional parameter to specify whether the R1C1 address notation instead of the usual A1 notation should be used.
  • In ADDRESS, the parameter is inserted as the fourth parameter, shifting the optional sheet name parameter to the fifth position.
  • In INDIRECT, the parameter is appended as the second parameter.
  • In both functions, if the argument is inserted with the value 0, then the R1C1 notation is used. If the argument is not given or has a value other than 0, then the A1 notation is used.
  • In case of R1C1 notation, ADDRESS returns address strings using the exclamation mark '!' as the sheet name separator, and INDIRECT expects the exclamation mark as sheet name separator. Both functions still use the dot '.' sheet name separator with A1 notation.
  • When opening documents from ODF 1.0/1.1 format, the ADDRESS functions that show a sheet name as the fourth parameter will shift that sheet name to become the fifth parameter. A new fourth parameter with the value 1 will be inserted.
  • When storing a document in ODF 1.0/1.1 format, if ADDRESS functions have a fourth parameter, that parameter will be removed.
Note pin.svg

Do not save a spreadsheet in the old ODF 1.0/1.1 format if the ADDRESS function's new fourth parameter was used with a value of 0.


Note pin.svg

The INDIRECT function is saved without conversion to ODF 1.0/1.1 format. If the second parameter was present, an older version of Calc will return an error for that function.


Examples:

Formula Description Returns
=INDIRECT(A1) where A1 cell contains C108 as a reference and cell C108 contains 100 The function returns the content of cell C108. 100
=INDIRECT(A1) where A1 cell contains "Rabbit" as a defined named and the reference to that defined name is to cell A1111 contains 1111 The function returns the content of cell C1111. 1111
=SUM(INDIRECT("a1:" & ADDRESS(1;3))) where A1,B1,C2 contains 1,2,3 The function totals the cells in the area of A1 up to the cell with the address defined by row 1 and column 3. This means that area A1:C1 is totaled. 6
=INDIRECT(A1;0) where A1 cell contains R108C3 as a reference and cell C108 contains 100 The function returns the content of cell C108 (R108C3 in R1C1 format). 100
Note pin.svg

If you open an Excel spreadsheet that uses indirect addresses calculated from string functions, the sheet addresses will not be translated automatically. For example, the Excel address in INDIRECT("[filename]sheetname!"&B1) is not converted into the Calc address in INDIRECT("filename#sheetname."&B1).


Related LibreOffice functions:

ADDRESS

ODF standard:

Section 6.14.7, part 2

Equivalent Excel functions:

INDIRECT