ADDRESS Function (OpenOffice Calc)

Use it when a report knows a row number and a column number but needs to display or construct a reference such as $AA$7. The function can also create a reference string after a lookup finds the position of a record. Combine it with INDIRECT only when you need to retrieve the actual cell value; generating the address alone does not retrieve the value.

Apache’s detailed reference documents the arguments in this order:

=ADDRESS(row; column; mode; type; sheet)

Only row and column are required. The optional trailing arguments may be omitted. For example:

=ADDRESS(4;3)
=ADDRESS(4;3;2)
=ADDRESS(4;3;2;;"Sheet2")

The two adjacent semicolons in the third example intentionally omit type while providing sheet in its fifth-argument position. These examples use the English-language Apache OpenOffice documentation’s semicolon argument separator; function names, decimal separators and argument separators can vary by locale. Do not insert a comma just because an Excel example uses it. Apache ADDRESS reference.

The four mode values

All these formulas use row 4 and column 3. The $ sign fixes that component when the resulting reference is used in a formula; it is part of the string generated by ADDRESS.

The mode mapping is explicit in Apache’s ADDRESS How To and its 4.1 Calc Guide chapter 13.

Create the following table by typing the exact entries into A1:D5 in a blank sheet. Leave D3:D5 empty. Enter each formula in E2:E5, respectively. These formulas refer to values in their own rows. Sheet2 is used only as an address label here, so that sheet need not exist just to display the text.

Why these are the expected results: Column 28 converts to AB (after Z comes AA, AB), and mode 4 leaves both the row and the column relative. Column 27 is AA; mode 3 fixes the column while leaving the row relative. The sheet-qualified Sheet2.C$4 example follows Apache’s documented A1-style dot convention. These conversions were independently calculated; they were not observed in a running Calc application.

If your Calc configuration expects a different argument separator, follow the separator shown by your installed program’s Function Wizard rather than assuming this English help example applies verbatim.

Make another small table, putting Name in A1 and Hours in B1:

Enter this formula outside the data range, for instance in D2:

=ADDRESS(MATCH("Bob";A2:A5;0)+ROW(A2)-1;2)

MATCH("Bob";A2:A5;0) locates Bob at position 3 in the range, not worksheet row 3. ROW(A2)-1 adds 1 to the position, giving worksheet row 4. Column 2 is B, and the default mode is fully absolute. The independently reasoned expected text is $B$4.

To return the value in that location rather than the address text, use:

=INDIRECT(ADDRESS(MATCH("Bob";A2:A5;0)+ROW(A2)-1;2))

The independently expected value is 12 (the contents of B4), assuming the source data and functions are evaluated as documented. Apache’s 4.1 guide to INDIRECT explicitly describes INDIRECT(ADDRESS(...)); the specific combination above was not native-tested. Adjust the ROW(A2)-1 offset if your data starts on a different worksheet row.

Further reference-style examples

The Apache-maintained Calc Help source explicitly documents ADDRESS(Row; Column; Abs; A1; "Sheet"). It explains that the A1/R1C1 switch was inserted in the fourth position and the sheet name moved to the fifth. This resolves the shorter four-field summaries in some guide pages: use the five-position signature when supplying the sheet name.

A fourth argument of 0 selects R1C1 text; an omitted or nonzero fourth argument selects A1. Apache documents a dot between sheet and cell for A1, and an exclamation mark for R1C1. The example =ADDRESS(4;3;2;;"Sheet2") intentionally leaves the fourth argument empty rather than moving the sheet name into that slot.

The same Help source warns about saving these formulas in the old ODF 1.0/1.1 format, where the fourth argument is removed. In particular, do not assume an R1C1 formula survives such an old-format conversion unchanged. This is a documented compatibility warning, not a round-trip test performed for this guide.

Limits: Treat ADDRESS as text construction, not a complete reference-validation workflow. When the target must actually exist, dereference it separately and handle any resulting error. The examples do not establish all sheet-existence, quoting, out-of-grid or type-coercion behaviour.

Apache Calc and Microsoft Excel agree on the basic idea of ADDRESS and on modes 1–4, but a sheet-qualified A1-style result differs: Apache documents Sheet2.A1, whereas Excel documents Sheet2!A1. The applications also differ in the presentation of separators, localization and potentially R1C1 compatibility. The Apache ADDRESS reference explicitly documents this Excel distinction, and Microsoft’s ADDRESS reference is a separate comparator, not proof of Apache behavior. Do not assume LibreOffice’s additional documentation or modern behavior proves feature support in Apache OpenOffice 4.1.16.

INDIRECT, MATCH, ROW, COLUMN, OFFSET, VLOOKUP.

The official Apache OpenOffice Calc reference establishes the function’s purpose and argument structure. The arguments and worked examples were editorially reviewed and the stated expected results were independently calculated.

Review date: 2026-10-12. No native application run: the worked outputs are documentation-based or independently calculated expectations, not measurements from installed Apache OpenOffice Calc. Ordinary numeric cases and the stated text-construction examples are checked; blank/text coercion, machine extremes and workbook round trips are not certified. The formula editor in your installed version is the authority for localized separators.

Example-check record · Editorial standards.