Documentation/How Tos/Calc: ADDRESS function
From Apache OpenOffice Wiki
< Documentation | How Tos
CALC FUNCTIONS
- Spreadsheet FunctionsSpreadsheet Lookup functions
- AddressChooseHlookupIndexIndirectLookupMathOffsetVlookupSpreadsheet Information functions
- AreasColumnColumnsErrortypeInfoRowRowsSheetSheetsOther functions
-
Contents
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
-
