Documentation/Calc Functions/SHEET

    From The Document Foundation Wiki
    Other languages:


    Function name:

    SHEET

    Category:

    Spreadsheet

    Summary:

    Determines the current sheet number for a given reference or named sheet.

    Sheet number refers to a sheet's current position in the left-to-right ordering of the tabs displayed in the Sheet tabs area at the bottom of Calc's main window.

    Syntax:

    SHEET([Reference])

    Returns:

    Returns a positive integer in the range 1 to the number of sheets in the spreadsheet, representing the given sheet's current position in the Sheet tabs area.

    Arguments:

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

    • If Reference is omitted, SHEET returns a value representing the current tab position for the sheet containing the cell in which the formula is entered.
    • If Reference is some form of reference (rather than a string), then it is not dereferenced. The contents of any cells within the referenced area has no significance.
    • If Reference is a string (in quotation marks) that is not the name of a sheet, then SHEET reports an invalid argument error (Err:502).
    • If Reference comprises multiple cell ranges, then SHEET reports a parameter list error (Err:504).
    • If Reference comprises a single cell range across more than one sheet, SHEET returns the position of the leftmost sheet in the range.

    Additional details:

    • 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 a sheet number, and the name of a hidden sheet can be passed to SHEET.
    • 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 SHEET. For example, =SHEET(Sheet1) may return 1 if it happens that Sheet1 is the leftmost entry in the Sheet tabs area; however, =SHEET(Sheet1) may return 12 if Sheet1 is the 12th entry from the left of the Sheet tabs area.

    Examples:

    Formula Description Returns
    =SHEET($Profits.A1:D120) 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 returns the value 3, because "Profits" is the third sheet from the left of the Sheet tabs area. If you hide the "Regions" and "Sales" sheets, the formula will continue to return 3. 3
    =SHEET(Products) where Products is a named range covering the cell range $'Product Data'.A1:F50 and the "Product Data" tab is currently tenth from the left of the Sheet tabs area. Here the function returns the value 10. 10
    =SHEET() where the formula is entered in a cell within the sheet that is currently fifth from the left of the Sheet tabs area. Here the function returns the number of the sheet containing the formula. 5
    =SHEET("Employee Data") where the tab for the "Employee Data" tab is currently in the fourth position in the Sheet tabs area. Here the function returns the number of the sheet named "Employee Data". 4
    =SHEET($Sales.A1:$Profits.F20) where the "Sales" and "Profits" tabs are currently in positions 4 and 3 respectively. Here the function returns the sheet number for the leftmost of the two tabs. 3

    Related LibreOffice functions:

    ROW

    COLUMN

    SHEETS

    ODF standard:

    Section 6.13.31, part 2

    Related (or similar) Excel functions:

    SHEET since v.2013.