Documentation/Calc Functions/ADDRESS

Function name:
ADDRESS

Category:
Spreadsheet

Summary:
Constructs a cell reference based on the specified row and column numbers, returning the reference as a text string. Optional arguments control:


 * Whether the cell reference should use relative or absolute addressing (see the Addresses and References, Absolute and Relative help page for more detail).
 * Whether the cell reference should use Calc A1 or Excel R1C1 formula syntax (see the Formula help page for more detail).
 * Whether the name of a sheet should be included in the cell reference.

The ADDRESS function is used to dynamically determine the reference of a cell of interest. The resulting string can then be passed to the INDIRECT function to retrieve the cell’s content.

Syntax:
ADDRESS(Row; Column[; Abs[; A1[; Sheet]]])

Returns:
Returns the cell reference constructed using the given arguments, as a text string.

Arguments:
Row is an integer value, or a reference to a cell containing an integer value, which is the row number to be used. A Calc spreadsheet may contain up to 1048576 rows.

Column is an integer value, or a reference to a cell containing an integer value, which is the column number to be used. A Calc spreadsheet may contain up to 1024 columns. Although by default Calc usually refers to columns using letters ("A" through to "AMJ"), ADDRESS only accepts numbers for this argument.

Abs is an integer value in the range 1 to 8, or a reference to a cell containing an integer value in that range, which determines how absolute or relative addressing is used. If Abs is omitted, the function uses the value 1. Although there are eight acceptable values for this argument, there are only four possible meanings, as shown in the table below.

A1 is a logical value, or a reference to a cell containing a logical value, which determines the formula syntax that should be used in the returned string. If A1 is set to 0 or FALSE, the Excel R1C1 formula syntax is used. If A1 is omitted or set to any other value, the Calc A1 formula syntax is used.

The following table shows the meaning of the various options for the Abs argument and shows typical outputs for different combinations of the Abs and A1 arguments, assuming that the Row and Column arguments are each set to 1.

Sheet is a text string (in double quotation marks), or a reference to a cell containing a text string, which is the name of the sheet to be appended at the beginning of the returned cell reference. The sheet name can include the name and location of a separate document. If omitted, no sheet name (or sheet separator) is included in the returned string and the resulting cell reference will be taken to be relative to the current sheet. Calc does not check that a sheet of the specified name currently exists. Note that if the Excel R1C1 formula syntax is selected (A1 set to either FALSE or 0) and Sheet is neither omitted nor an empty string, then ADDRESS returns a text string using an exclamation mark (!) as the sheet separator, instead of the dot (.) separator used with Calc A1 formula syntax.

The following conditions may be encountered:


 * If Row is non-numeric, then ADDRESS reports a #VALUE! error.
 * If Row is a non-integer real number, then ADDRESS truncates the value to the nearest integer.
 * For the majority of cases, if after truncation Row lies outside the range 1 ≤ Row ≤ 1048576, then ADDRESS reports an invalid argument error (Err:502).
 * However the allowed range of values for Row changes if the Excel R1C1 formula syntax is selected (A1 set to either FALSE or 0) and row relative addressing is selected (Abs set to 3, 4, 7, or 8). In these cases, negative row offsets can be specified (for example R[-1]C[1]). Now, if after truncation, Row lies outside the range -1048575 ≤ Row ≤ 1048575, ADDRESS reports an invalid argument error (Err:502). In addition, the same error may be reported if a value in this range appears to reference a row that does not exist in the sheet (for example, the formula  placed in cell A1 causes Err:502 but causes no error when placed in cell A2).


 * If Column is non-numeric, then ADDRESS reports a #VALUE! error.
 * If Column is a non-integer real number, then ADDRESS truncates the value to the nearest integer.
 * For the majority of cases, if after truncation Column lies outside the range 1 ≤ Column ≤ 1024, then ADDRESS reports an invalid argument error (Err:502).
 * However the allowed range of values for Column changes if the Excel R1C1 formula syntax is selected (A1 set to either FALSE or 0) and column relative addressing is selected (Abs set to 2, 4, 6, or 8). In these cases, negative column offsets can be specified (for example R[1]C[-1]). Now, if after truncation, Column lies outside the range -1023 ≤ Column ≤ 1023, ADDRESS reports an invalid argument error (Err:502). In addition, the same error may be reported if a value in this range appears to reference a column that does not exist in the sheet (for example, the formula  placed in cell A1 causes Err:502 but causes no error when placed in cell B1).


 * If Abs is non-numeric, then ADDRESS reports a #VALUE! error.
 * If Abs is a non-integer real number, then ADDRESS truncates the value to the nearest integer.
 * If after truncation Abs lies outside the range 1 ≤ Abs ≤ 8, then ADDRESS reports a #VALUE! error.


 * If A1 is non-numeric (and non-logical), then ADDRESS reports a #VALUE! error.

Additional details:

 * Calc's ADDRESS and INDIRECT functions are closely related. ADDRESS returns a cell reference as text, which can be passed as an argument to INDIRECT to retrieve the content of the referenced cell.


 * If you pass a string to INDIRECT that was generated by ADDRESS, make sure that the two functions use the same setting for the A1 argument. If the two functions assume a different formula syntax, errors may result.


 * In versions of the ODF prior to 1.2, the specification for the ADDRESS function did not include the A1 argument to differentiate between Calc A1 and Excel R1C1 formula syntax.
 * When opening a document that was saved in ODF 1.1/1.2 format, ADDRESS function calls that have a sheet name as their fourth argument will have that sheet name shifted to become the fifth argument. A new fourth argument with the value 1 will be inserted to select the default Calc A1 formula syntax.
 * When saving a document in ODF 1.1/1.2 format, ADDRESS function calls will have their fourth argument removed. In this way, any function calls that previously used the R1C1 formula syntax will revert back to default Calc A1 formula syntax.


 * Microsoft Excel represents some cell references differently to Calc, so this function is not always portable. For example, the formula  returns "Sheet2.A1" in Calc, whereas the equivalent in Excel returns "Sheet2!A1".

Related LibreOffice functions:
[[Special:MyLanguage/Documentation/Calc_Functions/INDIRECT INDIRECT]

ODF standard:
Section 6.14.2, part 2

Equivalent Excel functions:
ADDRESS