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

From Apache OpenOffice Wiki
Jump to: navigation, search
m
(updated to include the type parameter; removed long copy/pasted email from developer about this)
Line 5: Line 5:
  
 
=== Syntax: ===
 
=== Syntax: ===
−
<tt>'''INDIRECT(textref)'''</tt>
+
<tt>'''INDIRECT(textref; type)'''</tt>
  
 
: <tt>'''textref'''</tt> is a text string specifying the reference to be returned; the reference may be to a single cell or to a range.
 
: <tt>'''textref'''</tt> 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.
+
: The reference may then be used in formulas or functions requiring a reference - see the examples below.
 +
 
 +
 
 +
: <tt>'''type'''</tt> is an optional number; if <tt>'''0'''</tt>, <tt>'''textref'''</tt> is taken to be in [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]], and otherwise (or if omitted) <tt>'''textref'''</tt> is taken to be in the usual A1 notation.
 +
 
 +
 
 +
: A named reference is recognised with either <tt>'''type'''</tt>.
  
 
=== Example: ===
 
=== Example: ===
Line 17: Line 23:
 
<tt>'''=INDIRECT("B" & "2")'''</tt>
 
<tt>'''=INDIRECT("B" & "2")'''</tt>
 
: also returns the contents of cell B2. <tt>'''"B" & "2"'''</tt> becomes the text string <tt>'''"B2"'''</tt>.
 
: also returns the contents of cell B2. <tt>'''"B" & "2"'''</tt> becomes the text string <tt>'''"B2"'''</tt>.
 +
 +
<tt>'''=INDIRECT(D3)'''</tt>
 +
: where cell D3 contains the text <tt>'''B2'''</tt> returns the contents of cell B2.
  
 
<tt>'''=SUM(INDIRECT("A1:C3"))'''</tt>
 
<tt>'''=SUM(INDIRECT("A1:C3"))'''</tt>
 
: returns the sum of the numbers in A1:C3.
 
: returns the sum of the numbers in A1:C3.
  
−
=== Issues: ===
+
<tt>'''=INDIRECT("B2"; 99)'''</tt>
−
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''INDIRECT("Sheet2.A1")'''</tt> is valid in Calc, but in Excel the required form is <tt>'''INDIRECT("Sheet2!A1")'''</tt>. A portable solution might be <tt>'''INDIRECT(ADDRESS(1;1;4;"Sheet2"))'''</tt>.
+
: returns the contents of cell B2. The <tt>'''type'''</tt> is non-zero, so <tt>'''B2'''</tt> is evaluated as the usual A1 notation.
−
*Excel allows an R1C1 reference style; this will work in Calc from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
 
−
*Named cells/ranges are recognised in Calc from OOo2.4 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=4695 Issue 4695].
 
  
 +
<tt>'''=INDIRECT("R2C3"; 0)'''</tt>
 +
: returns the contents of cell C2. The <tt>'''type'''</tt> is zero, so <tt>'''R2C3'''</tt> is evaluated as R1C1 notation.
  
−
'''Additional optional parameter for OpenOffice.org 3:'''
+
<tt>'''=INDIRECT("R[3]C[1]"; 0)'''</tt>
 +
: entered in cell B1, returns the contents of cell C4. The <tt>'''type'''</tt> is zero, so <tt>'''R[3]C[1]'''</tt> is evaluated as R1C1 notation, in this case relative to cell B1: 3 rows down and 1 column across to yield a reference to cell C4.
  
−
For interoperability the [[Documentation/How Tos/Calc: ADDRESS function|ADDRESS]] and INDIRECT spreadsheet functions
+
=== Issues: ===
−
now support an additional optional parameter to specify whether the
+
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''INDIRECT("Sheet2.A1")'''</tt> is valid in Calc, but in Excel the required form is <tt>'''INDIRECT("Sheet2!A1")'''</tt>. A portable solution might be <tt>'''INDIRECT(ADDRESS(1;1;4;"Sheet2"))'''</tt>. In the case of R1C1 notation only, Calc uses the <tt>'''!'''</tt> character to separate the sheet identifier.
−
R1C1 address notation instead of the usual A1 notation should be used.
+
*The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
−
Though the R1C1 notation otherwise is not supported by the application
+
*Named cells/ranges are recognised in Calc from OOo2.4 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=4695 Issue 4695].
−
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
 
  
 
{{Documentation/SeeAlso|
 
{{Documentation/SeeAlso|
Line 79: Line 50:
 
* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]
 
* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]
  
 +
* [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]]
 
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 
[[Category: Documentation/Reference/Calc/Spreadsheet functions]]
 
[[Category: Documentation/Reference/Calc/Spreadsheet functions]]

Revision as of 15:45, 31 July 2010


INDIRECT

Returns a reference, given a text string.

Syntax:

INDIRECT(textref; type)

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 formulas or functions requiring a reference - see the examples below.


type is an optional number; if 0, textref is taken to be in R1C1 notation, and otherwise (or if omitted) textref is taken to be in the usual A1 notation.


A named reference is recognised with either type.

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".

=INDIRECT(D3)

where cell D3 contains the text B2 returns the contents of cell B2.

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

returns the sum of the numbers in A1:C3.

=INDIRECT("B2"; 99)

returns the contents of cell B2. The type is non-zero, so B2 is evaluated as the usual A1 notation.

=INDIRECT("R2C3"; 0)

returns the contents of cell C2. The type is zero, so R2C3 is evaluated as R1C1 notation.

=INDIRECT("R[3]C[1]"; 0)

entered in cell B1, returns the contents of cell C4. The type is zero, so R[3]C[1] is evaluated as R1C1 notation, in this case relative to cell B1: 3 rows down and 1 column across to yield a reference to cell C4.

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")). In the case of R1C1 notation only, Calc uses the ! character to separate the sheet identifier.
  • The type parameter is implemented from OOo3.0 - see Issue 91020.
  • Named cells/ranges are recognised in Calc from OOo2.4 - see Issue 4695.


Template:Documentation/SeeAlso

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_INDIRECT_function&oldid=176950"
Personal tools