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

From Apache OpenOffice Wiki
Jump to: navigation, search
(→‎Issues:: Excel type is a logical value)
Line 1: Line 1:
−
__NOTOC__
+
{{Documentation/MasterTOC
 +
|bookid=1234'''
 +
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
 +
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Spreadsheet_functions|<div style="font-size: 140%;">Spreadsheet Functions]]
 +
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_LOOKUP_function
 +
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_INDEX_function
 +
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Number_Conversion_functions
 +
|ShowNextPart=block|NextPart=Documentation/How_Tos/Calc:_Statistical_functions
 +
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>
  
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Spreadsheet Lookup functions</div>
 +
* [[Documentation/How_Tos/Calc:_ADDRESS_function|<div style="font-size: 120%;">Address]]
 +
* [[Documentation/How_Tos/Calc:_CHOOSE_function|<div style="font-size: 120%;">Choose]]
 +
* [[Documentation/How_Tos/Calc:_HLOOKUP_function|<div style="font-size: 120%;">Hlookup]]
 +
* [[Documentation/How_Tos/Calc:_INDEX_function|<div style="font-size: 120%;">Index]]
 +
* [[Documentation/How_Tos/Calc:_INDIRECT_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Indirect]]
 +
* [[Documentation/How_Tos/Calc:_LOOKUP_function|<div style="font-size: 120%;">Lookup]]
 +
* [[Documentation/How_Tos/Calc:_MATCH_function|<div style="font-size: 120%;">Math]]
 +
* [[Documentation/How_Tos/Calc:_OFFSET_function|<div style="font-size: 120%;">Offset]]
 +
* [[Documentation/How_Tos/Calc:_VLOOKUP_function|<div style="font-size: 120%;">Vlookup]]
 +
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Spreadsheet Information functions</div>
 +
* [[Documentation/How_Tos/Calc:_AREAS_function|<div style="font-size: 120%;">Areas]]
 +
* [[Documentation/How_Tos/Calc:_COLUMN_function|<div style="font-size: 120%;">Column]]
 +
* [[Documentation/How_Tos/Calc:_COLUMNS_function|<div style="font-size: 120%;">Columns]]
 +
* [[Documentation/How_Tos/Calc:_ERRORTYPE_function|<div style="font-size: 120%;">Errortype]]
 +
* [[Documentation/How_Tos/Calc:_INFO_function|<div style="font-size: 120%;">Info]]
 +
* [[Documentation/How_Tos/Calc:_ROW_function|<div style="font-size: 120%;">Row]]
 +
* [[Documentation/How_Tos/Calc:_ROWS_function|<div style="font-size: 120%;">Rows]]
 +
* [[Documentation/How_Tos/Calc:_SHEET_function|<div style="font-size: 120%;">Sheet]]
 +
* [[Documentation/How_Tos/Calc:_SHEETS_function|<div style="font-size: 120%;">Sheets]]
 +
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Other functions</div>
 +
* [[Documentation/How_Tos/Calc:_DDE_function|<div style="font-size: 120%;">Dde]]
 +
* [[Documentation/How_Tos/Calc:_HYPERLINK_function|<div style="font-size: 120%;">Hyperlink]]
 +
* [[Documentation/How_Tos/Calc:_STYLE_function|<div style="font-size: 120%;">Style]]
 +
}}__TOC__
 
== INDIRECT  ==
 
== INDIRECT  ==
 
Returns a reference, given a text string.  
 
Returns a reference, given a text string.  

Revision as of 18:58, 1 June 2013

CALC FUNCTIONS
FUNCTIONS
Spreadsheet Lookup functions
Spreadsheet Information functions
Other functions


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.
  • In Excel, type is a logical value; Calc also accepts logical values but they are displayed as numbers.
  • 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=225798"
Views
Personal tools
Navigation
Tools