Difference between revisions of "Documentation/How Tos/Calc: ADDRESS function"
From Apache OpenOffice Wiki
< Documentation | How Tos
(→Issues:) |
m |
||
| (12 intermediate revisions by 6 users not shown) | |||
| Line 1: | Line 1: | ||
| − | __NOTOC__ | + | {{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 == | ||
| Line 5: | Line 10: | ||
=== Syntax: === | === Syntax: === | ||
| − | <tt>'''ADDRESS(row; column; mode; sheet)'''</tt> | + | <tt>'''ADDRESS(row; column; mode; type; sheet)'''</tt> |
: <tt>'''row'''</tt> is a <u>number</u> specifying the row. | : <tt>'''row'''</tt> is a <u>number</u> specifying the row. | ||
| Line 12: | 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''' | |
| − | | | + | |
| − | | | + | |
| − | | | + | |
| − | + | ||
|-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;" /> | ||
</blockquote> | </blockquote> | ||
| − | : <tt>'''sheet'''</tt> is an optional <u>text string</u> specifying the sheet. | + | : <tt>'''type'''</tt> is an optional number; if <tt>'''0'''</tt>, <tt>'''ADDRESS'''</tt> returns [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]], and otherwise (or if omitted) <tt>'''ADDRESS'''</tt> returns the usual A1 notation. |
| + | |||
| + | : <tt>'''sheet'''</tt> is an optional <u>text string</u> specifying the sheet. In the usual A1 notation, Calc separates the sheet name with a <tt>'''.'''</tt> character; in the R1C1 notation, Calc separates the sheet name with a <tt>'''!'''</tt> character. | ||
=== Example: === | === Example: === | ||
| − | <tt>'''ADDRESS(4; 3; 2; "Sheet2")'''</tt> | + | <tt>'''ADDRESS(4; 3; 2;; "Sheet2")'''</tt> |
| − | : returns the text <tt>'''Sheet2.C$4'''</tt>. | + | : returns the text <tt>'''Sheet2.C$4'''</tt>. Note the omitted <tt>'''type'''</tt> parameter. |
<tt>'''ADDRESS(4; 3; 4)'''</tt> | <tt>'''ADDRESS(4; 3; 4)'''</tt> | ||
| Line 59: | Line 48: | ||
: returns the text <tt>'''$C$4'''</tt>. | : returns the text <tt>'''$C$4'''</tt>. | ||
| − | + | <tt>'''ADDRESS(4; 3; 2; 0; "Sheet2")'''</tt> | |
| − | ''' | + | : returns the text <tt>'''Sheet2!R4C[3]'''</tt>. |
| − | ''' | + | === 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>. | ||
| + | *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 ({{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. | ||
| − | [[Documentation/How_Tos/Calc: | + | {{SeeAlso|EN| |
| − | + | * [[Documentation/How_Tos/Calc: INDIRECT function|INDIRECT]]''' | |
| − | + | * [[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 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
-
