Difference between revisions of "Documentation/How Tos/Calc: INDIRECT function"

Revision as of 08:54, 24 November 2009

INDIRECT

Returns a reference, given a text string.

Syntax:

INDIRECT(textref)

textref is a text string specifying the reference to be returned; the reference may be to a single cell or to a range.

The reference may then be used in formulae or functions requiring a reference - see the examples below.

Example:

=INDIRECT("B2")

(in a cell) returns the contents of cell B2. INDIRECT("B2") returns a reference to cell B2, so this is the same as =B2.

=INDIRECT("B" & "2")

also returns the contents of cell B2. "B" & "2" becomes the text string "B2".

=SUM(INDIRECT("A1:C3"))

returns the sum of the numbers in A1:C3.

Issues:

• Excel represents some cell references differently to Calc, so this function is not always portable. For example INDIRECT("Sheet2.A1") is valid in Calc, but in Excel the required form is INDIRECT("Sheet2!A1"). A portable solution might be INDIRECT(ADDRESS(1;1;4;"Sheet2")).
• Excel allows an R1C1 reference style; this will work in Calc from OOo3.0 - see Issue 91020.
• Named cells/ranges are recognised in Calc from OOo2.4 - see Issue 4695.

Additional optional parameter for OpenOffice.org 3:

For interoperability the ADDRESS and INDIRECT spreadsheet functions now support an additional optional parameter to specify whether the R1C1 address notation instead of the usual A1 notation should be used. Though the R1C1 notation otherwise is not supported by the application yet, this enables imported spreadsheet documents to calculate formulas using it.

In ADDRESS, the parameter is _inserted_ as the 4th parameter, shifting the optional sheet name parameter to the 5th position.

In INDIRECT, the parameter is appended as the 2nd parameter.

In both functions, if the argument is given and 0 the R1C1 notation is used, if the argument is not given or has a value other than 0, the A1 notation is used. In case of R1C1 notation, ADDRESS produces 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 stored in ODF 1.0 or 1.1 format, an ADDRESS function appearing in a formula expression gets a 4th parameter of value 1 inserted if a sheet name was given as 4th parameter, shifting the sheet name to the 5th parameter.

When storing a document in ODF 1.0/1.1 format, if in an ADDRESS function a 4th parameter is present that parameter's expression will be stripped and not written. NOTE! This causes incompatibilities if the argument's expression calculated to 0, the function when loaded again will calculate a different result! A document should not be stored in the old ODF 1.0/1.1 format if the ADDRESS function's new 4th parameter was used with a value of 0.

The INDIRECT function is written as is to ODF 1.0/1.1 format; if the 2nd parameter was present, an older version of Calc will return an error for that function. This is on purpose, as ADDRESS usually is used in conjunction with INDIRECT, and when stored to ODF 1.0/1.1 format this combination will more likely return an error result upon recalculation if the R1C1 notation was used.

This change aligns with the behavior of other spreadsheet applications and the definition given in the OASIS ODFF/OpenFormula specification available at http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula

Template:Documentation/SeeAlso