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.
ADDRESS(Row; Column[; Abs[; A1[; "Sheet"]]])
Returns a cell address (reference) as text, according to the specified row and column numbers.
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|
|3||row relative, column absolute||$A1||RC1|
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
- 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.
|=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:
Equivalent Excel functions: