Difference between revisions of "Documentation/How Tos/Calc: ADDRESS function"
From Apache OpenOffice Wiki
< Documentation | How Tos
(New 'type' parameter; removal of pasted dev. message on this topic) |
m (→Issues:) |
||
| Line 67: | Line 67: | ||
*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 [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 ADDRESS 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. |
*The draft ODFF standard says that the default <tt>'''mode'''</tt> (if <tt>'''mode'''</tt> is omitted) should be <tt>'''4'''</tt>. Calc and Excel both default to <tt>'''1'''</tt>, so this may be a problem with ODFF. | *The draft ODFF standard says that the default <tt>'''mode'''</tt> (if <tt>'''mode'''</tt> is omitted) should be <tt>'''4'''</tt>. Calc and Excel both default to <tt>'''1'''</tt>, so this may be a problem with ODFF. | ||
Revision as of 14:43, 1 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.
- The draft ODFF standard says that the default mode (if mode is omitted) should be 4. Calc and Excel both default to 1, so this may be a problem with ODFF.
