Documentation/Calc Functions/ADDRESS
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
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.
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:
ODF standard:
Equivalent Excel functions:
ADDRESS