Difference between revisions of "Documentation/How Tos/Calc: ADDRESS function"
From Apache OpenOffice Wiki
< Documentation | How Tos
m (→Issues:) |
(→Issues:: ODFF issue (OFFICE-3261) resolved - removed from this help) |
||
Line 68: | Line 68: | ||
*The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020]. | *The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020]. | ||
*When storing a document in 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. A <tt>'''type'''</tt> value of <tt>'''1'''</tt> is allocated for any ODF 1.0/1.1 format document opened. | *When storing a document in 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. A <tt>'''type'''</tt> value of <tt>'''1'''</tt> is allocated for any ODF 1.0/1.1 format document opened. | ||
− | |||
{{Documentation/SeeAlso| | {{Documentation/SeeAlso| |
Revision as of 12:04, 2 August 2010
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 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. A type value of 1 is allocated for any ODF 1.0/1.1 format document opened.