Documentation/Calc Functions/ADDRESS

    From The Document Foundation Wiki

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    Other languages:


    Function name:

    ADDRESS

    Category:

    Spreadsheet

    Summary:

    Constructs a cell reference based on the specified row and column numbers (and, optionally, worksheet and file names), 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 details).
    • Whether the cell reference should use either Calc A1, or Excel R1C1 formula syntax (see the Formula help page for details).
    • Whether the name of a sheet (with or without a file name) 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 a text string representing a cell reference, constructed using the given arguments.

    Arguments:

    Row is an integer value, or a reference to a cell containing such value, which is the row number to be used. A Calc spreadsheet may contain up to 1,048,576 rows (see below for additional limitations).

    Column is an integer value, or a reference to a cell containing such value, which is the column number to be used. Although by default Calc usually refers to columns using letters (starting by "A" being column "1"), ADDRESS only accepts numbers for this argument. A Calc spreadsheet may contain up to 16384 columns (see below for additional limitations).


    Additional limitations:

    XLS(X) limitations
    File
    Format     		max ROWS max COLUMNS Comment
    XLSX 1048576 2^20 220 16384 2^14 214 XFD Excel 2007 and newer
    XLS 65536 2^16 216 256 2^8 28 IV Since Excel 97 to 2003
    XLS 16384 2^14 214 256 2^8 28 IV For Excel 5 and 95

    ODS limitations
    File
    Format     		max ROWS max COLUMNS Comment
    ODS 1048576 2^20 220 16384 2^14 214 XFD LibreOffice since version 7.4 included
    ODS 1048576 2^20 220 1024 2^10 210 AMJ LibreOffice before version 7.4
    ODS 1048576 2^20 220 1024 2^10 210 AMJ OOo since version 3.3 included
    ODS 65536 2^16 216 1024 2^10 210 AMJ OOo since version 3.0 included, before 3.3
    ODS 65536 2^16 216 256 2^8 28 IV OOo before version 3.0


    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 numeric 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.

    Cell reference output syntax according to Abs and A1 arguments
    Abs Meaning A1 = TRUE A1 = FALSE
    1, 5, or omitted Fully absolute $A$1 R1C1
    2 or 6 Row absolute, column relative A$1 R1C[1]
    3 or 7 Row relative, column absolute $A1 R[1]C1
    4 or 8 Fully relative A1 R[1]C[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 the Sheet argument is omitted, no sheet name (nor 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 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 its integer part.
    • For the majority of cases, after truncation, if Row resides outside the range 1 ≤ Row ≤ max (see the tables above), 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, after truncation, if Row resides outside the range -(max-1) ≤ Row ≤ (max-1), 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 =ADDRESS(-1; 1; 4; 0) placed in cell A1 causes Err:502 but causes no error when placed in cell A2).
    • In other words, the resulting absolute address should still be valid, within the normal constraints for Row of 1 ≤ Row ≤ max; otherwise an error is reported.
    • 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 its integer part.
    • For the majority of cases, after truncation, if Column resides outside the range 1 ≤ Column ≤ max (see the tables above), 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, after truncation, if Column resides outside the range -(max-1) ≤ Column ≤ (max-1), 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 =ADDRESS(1; -1; 4; 0) placed in cell A1 causes Err:502 but causes no error when placed in cell B1).
    • In other words, the resulting absolute address should still be valid, within the normal constraints for Column of 1 ≤ Column ≤ max; otherwise an error is reported.
    • 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 its integer part.
    • After truncation, if Abs resides 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:

    • The ADDRESS function returns a text string, and not a real reference. If the "referenced" cell is actually moved (thus modifying its reference), the ADDRESS function has no automatic method to keep track of such move. The ADDRESS function builds the resulting text string depending only on its own arguments.
    • 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.0/1.1 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.0/1.1 format, ADDRESS function calls will have their fourth argument removed. In this way, any function calls that previously used the Excel 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 ADDRESS(1; 1; 4; ; "Sheet2") returns "Sheet2.A1" in Calc, whereas the equivalent in Excel returns "Sheet2!A1".

    Examples:

    Data for examples
    A B C D
    1
    2 4
    3 3
    4 4
    5 0
    6 Sheet2
    7
    8 'file:///C:/my-spreadsheets/my-test.ods'#$Sheet1
    Formula Description Returns
    =ADDRESS(4; 3)

    =ADDRESS(4; 3; 1; TRUE(); "")

    Apply the default settings for the Abs (1: fully absolute) and A1 (TRUE: Calc A1 formula syntax) arguments. No value is supplied for the Sheet argument, so no sheet information is included in the returned cell reference.

    		$C$4
    =ADDRESS(4; 3; ; ; "Sheet2")

    The Abs and A1 arguments are omitted, so their default settings are again applied. A string is supplied for the Sheet argument, so that sheet information is included in the returned cell reference.

    		Sheet2.$C$4
    =ADDRESS(4; 3; 2; ; "Sheet2")

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

    The A1 argument is omitted, so its default setting is again applied. With the Abs argument set to 2 or to 6, a cell reference is returned that uses row absolute / column relative addressing.

    		Sheet2.C$4
    =ADDRESS(D2; D3; D4; D5; D6)

    All arguments are present and utilize values stored in cells. Since the A1 argument is set to 0, the function returns a cell reference based on the Excel R1C1 formula syntax.

    		Sheet2!R[4]C[3]
    =ADDRESS(10; 5; 1; ; D8)

    Returns a cell reference that includes a Windows-based file name and sheet name contained within the string defined for the Sheet argument (held in cell D8). Since Abs is 1, then the arguments (R10C5) result in the string "$E$10". If cell $E$10 of the specified sheet and file contains the number 123, then the formula =INDIRECT(ADDRESS(10; 5; 1; ; D8)) returns the value 123.

    		'file:///C:/my-spreadsheets/my-test.ods'#$Sheet1.$E$10

    Related LibreOffice functions:

    CELL

    COLUMN

    INDIRECT

    MATCH

    OFFSET

    ROW

    ODF standard:

    Section 6.14.2, part 2

    Related (or similar) Excel functions:

    ADDRESS


    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Documentation/Calc_Functions/ADDRESS&oldid=713358"