Documentation/How Tos/Calc: ADDRESS function

From Apache OpenOffice Wiki
< Documentation‎ | How Tos
Revision as of 07:42, 17 July 2018 by Sancho (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
CALC FUNCTIONS
FUNCTIONS
Spreadsheet Lookup functions
Spreadsheet Information functions
Other 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
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_ADDRESS_function&oldid=243394"
Views
Personal tools
Navigation
Print/export
Tools