Difference between revisions of "Documentation/How Tos/Calc: INDIRECT function"
From Apache OpenOffice Wiki
< Documentation | How Tos
m |
|||
| (16 intermediate revisions by 8 users not shown) | |||
| Line 1: | Line 1: | ||
| − | __NOTOC__ | + | {{DISPLAYTITLE:INDIRECT function}} |
| + | {{Documentation/CalcFunc SpreadsheetTOC | ||
| + | |ShowPrevNext=block | ||
| + | |PrevPage=Documentation/How_Tos/Calc:_INDEX_function | ||
| + | |NextPage=Documentation/How_Tos/Calc:_LOOKUP_function | ||
| + | }}__NOTOC__ | ||
== INDIRECT == | == INDIRECT == | ||
| Line 5: | Line 10: | ||
=== 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 | + | : 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 recognized with either <tt>'''type'''</tt>. | ||
=== Example: === | === Example: === | ||
| Line 17: | Line 28: | ||
<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. | ||
| − | = | + | <tt>'''=INDIRECT("B2"; 99)'''</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. |
| + | |||
| + | <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. | ||
| − | '''[[ | + | <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. | ||
=== 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>. In the case of R1C1 notation only, Calc uses the <tt>'''!'''</tt> character to separate the sheet identifier. |
| − | *Named cells/ranges | + | *The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see {{bug|91020}}. |
| + | *In Excel, <tt>'''type'''</tt> is a logical value; Calc also accepts logical values, but they are displayed as numbers. | ||
| + | *Named cells/ranges are recognized in Calc from OOo2.4 - see {{bug|4695}}. | ||
| + | |||
| + | |||
| + | {{SeeAlso|EN| | ||
| + | * [[Documentation/How_Tos/Calc: ADDRESS function|ADDRESS]] | ||
| + | |||
| + | * [[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 by category|Functions listed by category]]}} | ||
| + | [[Category: Documentation/Reference/Calc/Spreadsheet functions]] | ||
Latest revision as of 11:02, 2 February 2024
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 recognized 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 .
- In Excel, type is a logical value; Calc also accepts logical values, but they are displayed as numbers.
- Named cells/ranges are recognized in Calc from OOo2.4 - see Issue 4695 .
See Also
