# 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