Documentation/Calc Functions/SHEETS

    From The Document Foundation Wiki
    Other languages:


    Function name:

    SHEETS

    Category:

    Spreadsheet

    Summary:

    Determines the number of sheets in a given reference, given the current arrangement of tabs in the Sheet tabs area at the bottom of Calc's main window. If you do not specify a reference, returns the number of sheets currently in the spreadsheet.

    Syntax:

    SHEETS([Reference])

    Returns:

    Returns a positive integer that is the current number of sheets in the given reference or the spreadsheet.

    Arguments:

    Reference is a specific cell reference (for example, "$'Employee Data'.A4"), a specific cell range (for example, "$Sales.A3:D12"), or the name of a named or database range.

    • If Reference is omitted, SHEETS returns the total number of sheets currently in the spreadsheet.
    • Reference is not dereferenced – the contents of any cells within the referenced area has no significance.
    • If Reference comprises multiple cell ranges, then SHEETS reports a parameter list error (Err:504).

    Additional details:

    • If a valid Reference argument is supplied, SHEETS returns the current number of sheets within that reference. If the arrangement of tabs in Sheet tabs area is subsequently changed, SHEETS could then return a different value.
    • Calc provides a facility to hide a sheet, so that its tab is not visible in the Sheet tabs area at the bottom of Calc's main window. Hidden sheets are taken into account when determining the number of sheets in a reference and the name of a hidden sheet can be used in the Reference argument.
    • A common cause of confusion arises from the default naming applied by Calc to new sheets, for example Sheet1, Sheet2, Sheet3, and so on. There is no relationship between any digit(s) that appear in the name of a sheet and the value returned by SHEETS. For example, =SHEETS($Sheet1.A1:$Sheet3.E100) may return 3 if it happens that Sheet1, Sheet2, and Sheet3 are the three entries at the left of the Sheet tabs area; however, the same formula may return some other value if the tabs in the Sheet tabs area are arranged differently.

    Examples:

    Formula Description Returns
    =SHEETS($Regions.A1:$Profits.G12) where the first three tabs currently at the left end of the Sheet tabs area are named "Regions", "Sales", and "Profits" (left to right). Here the function counts three sheets, even if one or more of those sheets is hidden. 3
    =SHEETS(C3) Here the function counts only the sheet in which the formula cell is located. The formula =SHEETS($Sales.C4) also returns 1. 1
    =SHEETS() where the spreadsheet currently contains 3 sheets. Since the Reference argument is omitted, here the function counts all sheets in the spreadsheet and returns the value 3. 3
    =SHEETS(My_Range) where "My_Range" is a named range covering $Sheet1.A1:$Sheet4.G12, and "Sheet1", "Sheet2", "Sheet3", and "Sheet4" are the first four tabs currently at the left end of the Sheet tabs area. Here we are using a three-dimensional reference across four sheets and the function returns 4. 4

    Related LibreOffice functions:

    COLUMNS

    ROWS

    SHEET

    ODF standard:

    Section 6.13.32, part 2

    Related (or similar) Excel functions:

    SHEETS since v.2013.