Difference between revisions of "Documentation/How Tos/Calc: ADDRESS function"
From Apache OpenOffice Wiki
< Documentation | How Tos
Javier Lopez (Talk | contribs) |
m |
||
(One intermediate revision by one other user not shown) | |||
Line 1: | Line 1: | ||
− | {{ | + | {{DISPLAYTITLE:ADDRESS function}} |
− | + | {{Documentation/CalcFunc SpreadsheetTOC | |
− | + | |ShowPrevNext=block | |
− | + | |PrevPage=Documentation/How_Tos/Calc:_Spreadsheet_functions | |
− | | | + | |NextPage=Documentation/How_Tos/Calc:_CHOOSE_function |
− | + | }}__NOTOC__ | |
− | | | + | |
− | + | ||
− | + | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
== ADDRESS == | == ADDRESS == | ||
Returns a cell reference as text, given row and column numbers. | Returns a cell reference as text, given row and column numbers. | ||
Line 47: | Line 17: | ||
: <tt>'''mode'''</tt> (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be <tt>'''1'''</tt>. | : <tt>'''mode'''</tt> (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be <tt>'''1'''</tt>. | ||
+ | |||
<blockquote> | <blockquote> | ||
{| border="0" cellpadding="0" cellspacing="10" align="left" | {| border="0" cellpadding="0" cellspacing="10" align="left" | ||
|-valign="top" align="center" | |-valign="top" align="center" | ||
− | + | !mode||row||column||example''' | |
− | |row | + | |
− | |column | + | |
− | |example''' | + | |
− | + | ||
|-valign="top" align="center" | |-valign="top" align="center" | ||
− | |<tt>'''1'''</tt> | + | |<tt>'''1'''</tt>||absolute||absolute||$A$1 |
− | |absolute | + | |
− | |absolute | + | |
− | |$A$1 | + | |
− | + | ||
|-valign="top" align="center" | |-valign="top" align="center" | ||
− | |<tt>'''2'''</tt> | + | |<tt>'''2'''</tt>||absolute||relative||A$1 |
− | |absolute | + | |
− | |relative | + | |
− | |A$1 | + | |
− | + | ||
|-valign="top" align="center" | |-valign="top" align="center" | ||
− | |<tt>'''3'''</tt> | + | |<tt>'''3'''</tt>||relative||absolute||$A1 |
− | |relative | + | |
− | |absolute | + | |
− | |$A1 | + | |
− | + | ||
|-valign="top" align="center" | |-valign="top" align="center" | ||
− | |<tt>'''4'''</tt> | + | |<tt>'''4'''</tt>||relative||relative||A1 |
− | |relative | + | |
− | |relative | + | |
− | |A1 | + | |
|} | |} | ||
<br style="clear:both;" /> | <br style="clear:both;" /> | ||
Line 101: | Line 53: | ||
=== Issues: === | === Issues: === | ||
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''ADDRESS(1;1;4;;"Sheet2")'''</tt> returns <tt>'''Sheet2.A1'''</tt> in Calc; the equivalent in Excel returns <tt>'''Sheet2!A1'''</tt>. | *Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''ADDRESS(1;1;4;;"Sheet2")'''</tt> returns <tt>'''Sheet2.A1'''</tt> in Calc; the equivalent in Excel returns <tt>'''Sheet2!A1'''</tt>. | ||
− | *The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see | + | *The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see {{bug|91020}}. |
− | *When storing a document in the older ODF 1.0/1.1 format, any <tt>'''type'''</tt> parameter in an <tt>'''ADDRESS'''</tt> function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. From OOo 3.0, Calc writes in ODF 1.2 draft format as standard, although this can be changed (Tools | + | *When storing a document in the older ODF 1.0/1.1 format, any <tt>'''type'''</tt> parameter in an <tt>'''ADDRESS'''</tt> function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. From OOo 3.0, Calc writes in ODF 1.2 draft format as standard, although this can be changed ({{menu|Tools|Options|Load/Save|General}}). A <tt>'''type'''</tt> value of <tt>'''1'''</tt> is allocated for any ODF 1.0/1.1 format document opened. |
− | {{ | + | {{SeeAlso|EN| |
* [[Documentation/How_Tos/Calc: INDIRECT function|INDIRECT]]''' | * [[Documentation/How_Tos/Calc: INDIRECT function|INDIRECT]]''' | ||
Latest revision as of 10:52, 2 February 2024
- Spreadsheet FunctionsSpreadsheet Lookup functions
- ADDRESSCHOOSEHLOOKUPINDEXINDIRECTLOOKUPMATHOFFSETVLOOKUPSpreadsheet Information functions
- AREASCOLUMNCOLUMNSERRORTYPEINFOROWROWSSHEETSHEETSOther functions
-
ADDRESS
Returns a cell reference as text, given row and column numbers.
Syntax:
ADDRESS(row; column; mode; type; sheet)
- row is a number specifying the row.
- column is a number (not a letter) specifying the column.
- mode (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be 1.
mode row column example 1 absolute absolute $A$1 2 absolute relative A$1 3 relative absolute $A1 4 relative relative A1
- type is an optional number; if 0, ADDRESS returns R1C1 notation, and otherwise (or if omitted) ADDRESS returns the usual A1 notation.
- sheet is an optional text string specifying the sheet. In the usual A1 notation, Calc separates the sheet name with a . character; in the R1C1 notation, Calc separates the sheet name with a ! character.
Example:
ADDRESS(4; 3; 2;; "Sheet2")
- returns the text Sheet2.C$4. Note the omitted type parameter.
ADDRESS(4; 3; 4)
- returns the text C4.
ADDRESS(4; 3)
- returns the text $C$4.
ADDRESS(4; 3; 2; 0; "Sheet2")
- returns the text Sheet2!R4C[3].
Issues:
- Excel represents some cell references differently to Calc, so this function is not always portable. For example ADDRESS(1;1;4;;"Sheet2") returns Sheet2.A1 in Calc; the equivalent in Excel returns Sheet2!A1.
- The type parameter is implemented from OOo3.0 - see Issue 91020 .
- When storing a document in the older ODF 1.0/1.1 format, any type parameter in an ADDRESS function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. From OOo 3.0, Calc writes in ODF 1.2 draft format as standard, although this can be changed (Tools → Options → Load/Save → General). A type value of 1 is allocated for any ODF 1.0/1.1 format document opened.
See Also
-