ADDRESS Function (LibreOffice Calc)

• Builds a cell reference string from row/column numbers
• Supports absolute, relative, and mixed references
• Can target specific sheets
• Often used with INDIRECT to create dynamic formulas
• Useful for automation, dashboards, and programmatic cell targeting

ADDRESS(row; column; [abs]; [a1]; [sheet])

Arguments

• row:
  Row number (1 or greater).

• column:
  Column number (1 = A, 2 = B, etc.).

• abs (optional):
  Reference type:

  • 1 = $A$1 (absolute row, absolute column)
  • 2 = A$1 (absolute row, relative column)
  • 3 = $A1 (relative row, absolute column)
  • 4 = A1 (relative row, relative column)

  Default: 1

• a1 (optional):
  Reference style:

  • TRUE = A1 style
  • FALSE = R1C1 style

  Default: TRUE

• sheet (optional):
  Sheet name to prefix (text).

Create a simple reference

=ADDRESS(3; 2)
→ "$B$3"

Relative reference

=ADDRESS(3; 2; 4)
→ "B3"

Mixed reference

=ADDRESS(3; 2; 2)
→ "B$3"

R1C1 style

=ADDRESS(3; 2; 1; FALSE)
→ "R3C2"

Reference on another sheet

=ADDRESS(5; 4; 1; 1; "Data")
→ "Data.$D$5"

Build a dynamic reference with INDIRECT

=INDIRECT(ADDRESS(A1; B1))

Get the last row in a column

=ADDRESS(MAX((A:A<>"")*ROW(A:A)); 1)

Build a reference to a moving range

=ADDRESS(A1; B1) & ":" & ADDRESS(A2; B2)

Convert column number to letter

=SUBSTITUTE(ADDRESS(1; A1; 4); "1"; "")

Reference the same cell on another sheet dynamically

=ADDRESS(ROW(); COLUMN(); 1; 1; C1)

ADDRESS returns a text string, not a reference

To use the result as a reference, wrap with INDIRECT.

Accepts:

• Positive integers
• Expressions that evaluate to numbers
• Sheet names with spaces (must be quoted)

Invalid input → Err:502

Behavior details

• Column numbers beyond 16384 (XFD) are invalid
• Sheet names with spaces require 'Sheet Name'
• ADDRESS does not validate whether the referenced cell exists

ADDRESS of an error → error propagates

• Use ADDRESS + INDIRECT for dynamic formulas
• Use ADDRESS to generate column letters programmatically
• Prefer INDEX for performance‑critical dynamic references
• Always validate row/column bounds
• Use sheet argument for multi‑sheet automation

• Use INDIRECT to convert ADDRESS output into a real reference
• Use ROW, COLUMN, INDEX, and OFFSET for dynamic lookups
• Use CHOOSECOLS and CHOOSEROWS for structured range extraction
• Use MATCH to locate row/column numbers programmatically

By mastering ADDRESS and its companion functions, you can build powerful, dynamic, and fully automated spreadsheet systems in LibreOffice Calc.