Documentation/Calc Functions/ADDRESS

From The Document Foundation Wiki
Jump to: navigation, search

Function name:

ADDRESS

Category:

Spreadsheet

Summary:

Returns a cell address (reference) as text. You can determine whether the address is interpreted as an absolute address (for example, $A$1) or as a relative address (as A1) or in a mixed form (A$1 or $A1). You can also specify the name of the sheet.

Syntax:

ADDRESS(Row; Column[; Abs[; A1[; "Sheet"]]])

Returns:

Returns a cell address (reference) as text, according to the specified row and column numbers.

Arguments:

Row is a non-negative integer or a reference to the cell containing that value which represents the row number for the cell reference. (the number, not the letter)

Column is a non-negative integer or a reference to the cell containing that value which represents the column number for the cell reference. (the number, not the letter)

Abs is a integer value ranging from 1 to 4 or a reference to the cell containing that value which determines the type of reference:

Abs Meaning A1 = TRUE() A1 = FALSE()
1 or missing fully absolute $A$1 R1C1
2 row absolute, column relative A$1 R1C[1]
3 row relative, column absolute $A1 R[1]C1
4 fully relative A1 R[1]C[1]

If omitted, the function uses the value 1.

A1 if set to 0 or FALSE, the R1C1 notation is used. If this parameter is omitted or set to another value than 0 like 1,2,3,4,TRUE..., the A1 notation is used.

Sheet represents the name of the sheet. It must be placed in double-quotes.

  • If Row,Column, Abs or A1 are a non-integer real number, then they are truncated to an integer value.
  • If after truncation Row is less than 1 or greater than 1048576 then the function returns an error value.
  • If after truncation Column is less than 1 or greater than 1024 then the function returns an error value.
  • If after truncation Abs is less than 1 or greater than 8 then the function returns a #VALUE! error.
  • If either of Row, Column, Abs or A1 is a non-numeric and non-logical value then the function returns a #VALUE! error

Additional details:

  • For interoperability, the ADDRESS and INDIRECT functions support an optional parameter to specify whether the R1C1 address notation instead of the usual A1 notation should be used.
  • In ADDRESS, the parameter is inserted as the fourth parameter, shifting the optional sheet name parameter to the fifth position.
  • In INDIRECT, the parameter is appended as the second parameter.
  • In both functions, if the argument is inserted with the value 0, then the R1C1 notation is used. If the argument is not given or has a value other than 0, then the A1 notation is used.
  • In the case of R1C1 notation, ADDRESS returns address strings using the exclamation mark '!' as the sheet name separator, and INDIRECT expects the exclamation mark as a sheet name separator. Both functions still use the dot '.' sheet name separator with A1 notation.
  • When opening documents from ODF 1.0/1.1 format, the ADDRESS functions that show a sheet name as the fourth parameter will shift that sheet name to become the fifth parameter. A new fourth parameter with the value 1 will be inserted.
  • When storing a document in ODF 1.0/1.1 format, if ADDRESS functions have a fourth parameter, that parameter will be removed.
Note pin.svg

Do not save a spreadsheet in the old ODF 1.0/1.1 format if the ADDRESS function's new fourth parameter was used with a value of 0.


Note pin.svg

The INDIRECT function is saved without conversion to ODF 1.0/1.1 format. If the second parameter was present, an older version of Calc will return an error for that function.


Examples:

Formula Description Returns
=ADDRESS(5;3;3;4;"Sheet3") Th function returns a row relative column absolute addressing reference to another worksheet in A1 notation. If this formula is in cell B2 of Sheet1, and the cell C5 in sheet 2 contains the value -6, you can refer indirectly to the referenced cell using a function in B2 by entering =ABS(INDIRECT(B2)). The result is the absolute value of the cell reference specified in B2, which in this case is 6. Sheet3.$C5
=ADDRESS(5;3;;0;"MyCalc") Th function returns the absolute reference addressing (by default) to another worksheet in R1C1 notation. MyCalc!R5C3
=ADDRESS(5;3;;0;"[BOOK]MyCalc") The function returns the absolute reference addressing (by default) to another worksheet in R1C1 notation. '[BOOK]MyCalc'!R2C3
=ADDRESS(5;3;4;1) The function returns the fully relative addressing in current worksheet (default) in A1 notation. C5

Related LibreOffice functions:

INDIRECT

ODF standard:

Section 6.14.2, part 2

Equivalent Excel functions:

ADDRESS