Documentation/Calc Functions/INDIRECT

    From The Document Foundation Wiki

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

    Other languages:


    Function name:

    INDIRECT

    Category:

    Spreadsheet

    Summary:

    Creates a reference based on a string representation of that reference. The reference may be to a single cell or to a range of cells, and can optionally include a worksheet reference too, with or without a file name.

    An optional argument controls whether the input string uses the Calc A1 or Excel R1C1 formula syntax (see the Formula help page for details).

    The ADDRESS function is used to dynamically determine the reference of a cell of interest, in the form of a text string. The resulting string can then be passed to the INDIRECT function to retrieve the cell's content. However, the string passed to INDIRECT can be created/built by other methods, not necessarily by the ADDRESS function.

    Syntax:

    INDIRECT(Reference[; A1])

    Returns:

    Returns a reference to one or more cells, based on the given arguments.

    Arguments:

    Reference is a text string, or a reference to a cell containing a text string, which is a string representation of the reference.

    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.

    • If A1 is non-numeric (and non-logical), then INDIRECT reports a #VALUE! error.
    • If the combination of the Reference and A1 arguments do not correspond to a valid reference, then INDIRECT reports a #REF! error.
    • If the INDIRECT function call relates to an external link, then you may receive a suitable message on opening your spreadsheet, warning that automatic update of external links has been disabled. At the same time Err:540 will be displayed in the affected cell. Click the Allow updating button to enable updating of these links.

    Additional details:

    • INDIRECT is a volatile function. This means that Calc will update the reference returned by INDIRECT when any cell is updated by selecting Data ▸ Calculate ▸ Recalculate or pressing F9, or on any input event.
    • 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 INDIRECT function did not include the A1 argument to differentiate between Calc A1 and Excel R1C1 formula syntax. When saving a document in ODF 1.0/1.1 format, calls to the INDIRECT function are saved without conversion. This may lead to subsequent errors appearing for those function calls.
    • Microsoft Excel represents some cell references differently than Calc, so this function is not always portable. For example, the formula =INDIRECT("Sheet2.A1") is valid in Calc (where the worksheet name separator is the dot "."), but the form required in Excel is =INDIRECT("Sheet2!A1") (where the worksheet name separator is the exclamation mark "!").
    • If you open an Excel spreadsheet that uses indirect addresses calculated from string functions, the sheet addresses will not be translated automatically. For example, the Excel address in =INDIRECT("'[filename]sheetname'!"&B1) is not converted into the Calc address in =INDIRECT("'filename#sheetname'."&B1).

    Examples:

    Data for examples
    A B C D
    1 1 2 6 =INDIRECT("R[2]C[-1]"; 0)
    2 3 4 5
    3 7 B2
    4 "myCellRange": A1:B2  ⇐ named range
    Formula Description Returns
    =INDIRECT("B2") Equivalent to =B2. Note the use of quotation marks, enclosing a text string. 4
    =INDIRECT("B" & "2") Equivalent to =B2. 4
    =INDIRECT(D3) Equivalent to =B2. Note the lack of quotation marks; the argument is a cell reference. 4
    =INDIRECT(B2) Note the lack of quotation marks. Since the referenced cell does not contain a text string representing a cell address, the result is an error. #REF!
    =INDIRECT("B2"; 99) Equivalent to =B2. The A1 argument is a non-zero numeric value, equivalent to omited. 4
    =SUM(INDIRECT("A1:B2")) First return a reference to the cell range A1:B2; the formula therefore returns the sum of the four numbers in these cells. 10
    =SUM(INDIRECT("myCellRange")) First return a reference to the cell range A1:B2; the formula therefore returns the sum of the four numbers in these cells. 10
    =INDIRECT("R2C3"; 0) Note the use of quotation marks, enclosing a text string, and value zero for the A1 argument. Return the contents of cell R2C3 (C2). 5
    =INDIRECT("R[2]C[-1]"; 0)

    where the formula is entered in cell D1.

    		The A1 argument is 0, so R[2]C[-1] is evaluated in R1C1-formula syntax, in this case relative to cell D1; 2 rows down and -1 column across, so as to yield a reference to cell C3. 7
    =SUM(INDIRECT("a1:" & ADDRESS(1;3))) Sum the cells in the range from cell A1 to the cell whose address is defined by row 1 and column 3 (C1). 9

    Related LibreOffice functions:

    ADDRESS

    COLUMN

    ROW

    ODF standard:

    Section 6.14.7, part 2

    Related (or similar) Excel functions:

    INDIRECT


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