Documentation/Calc Functions/INDIRECT

Function name:
INDIRECT

Category:
Spreadsheet

Summary:
Creates a reference from a string representation of that reference. The reference may be to a single cell or to a range of cells.

An optional argument controls whether the input string uses the Calc A1 or Excel R1C1 formula syntax (see the Formula help page for more detail).

The ADDRESS function is used to dynamically determine the reference of a cell of interest. The resulting string can then be passed to the INDIRECT function to retrieve the cell’s content. However, the string passed to INDIRECT need not be created using ADDRESS.

Syntax:
INDIRECT(Reference[; A1])

Returns:
Returns a reference to one or more cells, based in the given arguments.

Arguments:
Reference is a text string, or a reference to a cell containing a text string, which is a string representation of the reference.

A1 is a logical value, or a reference to a cell containing a logical value, which determines the formula syntax that should be used in the returned string. If A1 is set to 0 or FALSE, the Excel R1C1 formula syntax is used. If A1 is omitted or set to any other value, the Calc A1 formula syntax is used.


 * If A1 is non-numeric (and non-logical), then INDIRECT reports a #VALUE! error.
 * If the combination of the Reference and A1 arguments do not correspond to a valid reference, then INDIRECT reports a #REF! rrror.
 * If the INDIRECT function call relates to an external link, then you may receive a suitable message on opening your spreadsheet, warning that automatic update of external links has been disabled. At the same time Err:540 will be displayed in the affected cell. Click the Allow updating button to enable updating of these links.

Additional details:

 * INDIRECT is a volatile function. This means that Calc will update the reference returned by INDIRECT when any cell is updated by selecting or pressing F9, or on any input event.
 * Calc's ADDRESS and INDIRECT functions are closely related. ADDRESS returns a cell reference as text, which can be passed as an argument to INDIRECT to retrieve the content of the referenced cell.
 * If you pass a string to INDIRECT that was generated by ADDRESS, make sure that the two functions use the same setting for the A1 argument. If the two functions assume a different formula syntax, errors may result.
 * In versions of the ODF prior to 1.2, the specification for the INDIRECT function did not include the A1 argument to differentiate between Calc A1 and Excel R1C1 formula syntax. When saving a document in ODF 1.1/1.2 format, calls to the INDIRECT function are saved without conversion. This may lead to subsequent errors appearing for those function calls.
 * Microsoft Excel represents some cell references differently to Calc, so this function is not always portable. For example, the formula  is valid in Calc, but the form required in Excel is.
 * 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  is not converted into the Calc address in.

Related LibreOffice functions:
ADDRESS

ODF standard:
Section 6.14.7, part 2

Equivalent Excel functions:
INDIRECT