Documentation/How Tos/Calc: ADDRESS function
ADDRESS
Returns a cell reference as text, given row and column numbers.
Syntax:
ADDRESS(row; column; mode; 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
- sheet is an optional text string specifying the sheet.
Example:
ADDRESS(4; 3; 2; "Sheet2")
- returns the text Sheet2.C$4.
ADDRESS(4; 3; 4)
- returns the text C4.
ADDRESS(4; 3)
- returns the text $C$4.
See also:
Functions listed alphabetically, Functions listed by category
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.
- Excel allows an R1C1 reference style; this will work in Calc from OOo3.0 - see Issue 91020.
- 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.
Additional optional parameter for OpenOffice.org 3:
For interoperability the ADDRESS and INDIRECT spreadsheet functions now support an additional optional parameter to specify whether the R1C1 address notation instead of the usual A1 notation should be used. Though the R1C1 notation otherwise is not supported by the application yet, this enables imported spreadsheet documents to calculate formulas using it.
In ADDRESS, the parameter is _inserted_ as the 4th parameter, shifting the optional sheet name parameter to the 5th position.
In INDIRECT, the parameter is appended as the 2nd parameter.
In both functions, if the argument is given and 0 the R1C1 notation is used, if the argument is not given or has a value other than 0, the A1 notation is used. In case of R1C1 notation, ADDRESS produces address strings using the exclamation mark '!' as the sheet name separator and INDIRECT expects the exclamation mark as sheet name separator. Both functions still use the dot '.' sheet name separator with A1 notation.
When opening documents stored in ODF 1.0 or 1.1 format, an ADDRESS function appearing in a formula expression gets a 4th parameter of value 1 inserted if a sheet name was given as 4th parameter, shifting the sheet name to the 5th parameter.
When storing a document in ODF 1.0/1.1 format, if in an ADDRESS function a 4th parameter is present that parameter's expression will be stripped and not written. NOTE! This causes incompatibilities if the argument's expression calculated to 0, the function when loaded again will calculate a different result! A document should not be stored in the old ODF 1.0/1.1 format if the ADDRESS function's new 4th parameter was used with a value of 0.
The INDIRECT function is written as is to ODF 1.0/1.1 format; if the 2nd parameter was present, an older version of Calc will return an error for that function. This is on purpose, as ADDRESS usually is used in conjunction with INDIRECT, and when stored to ODF 1.0/1.1 format this combination will more likely return an error result upon recalculation if the R1C1 notation was used.
This change aligns with the behavior of other spreadsheet applications and the definition given in the OASIS ODFF/OpenFormula specification available at http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula
