Difference between revisions of "Documentation/How Tos/Calc: INDIRECT function"
From Apache OpenOffice Wiki
< Documentation | How Tos
(→Issues:) |
(→Issues:) |
||
| Line 31: | Line 31: | ||
=== Issues: === | === Issues: === | ||
*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>. | *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>. | ||
| − | *Excel | + | *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 | + | *Named cells/ranges are recognised in Calc from OOo2.4 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=4695 Issue 4695]. |
Revision as of 05:47, 16 September 2008
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.
See also:
Functions listed alphabetically, Functions listed by category
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.
