The SHEET and SHEETS functions were added in Excel 2013. The SHEETS function counts all the sheets in a reference, and the SHEET function returns the sheet number for a reference.
Among the new functions in Excel 2013 are SHEET and SHEETS.
To see how you can use the new SHEET and SHEETS functions in Excel 2013, please watch this short video tutorial.
You can use the SHEET and SHEETS formulas to do some troubleshooting in a workbook.
The SHEETS function tells you how many sheets are in a reference. In the screen shot below, this formula:
will return 3, as the number of sheets in that 3D reference.
In the formula above, the SHEETS function told us how many sheets were in the reference. We could use the COUNT or COUNTA function to see if there is a value in each sheet.
Then, subtract the count from the number of sheets, to see if there are any missing values.
In the worksheet shown above, there are 3 sheets in the reference, but only 2 entries. You can check the sheets, to find the missing value.
In the SHEETS function, if you omit the reference, it tells you how many sheets are in the workbook -- visible, hidden or very hidden sheets.
In the screen shot below, the result is 8, but you can only see 4 sheet tabs.
If the SHEETS result isn't the same as the number of visible tabs, you'll know that it's time to look for hidden sheets.
Unfortunately, the SHEET function doesn't work with multiple references, such as:
And, you can't use INDIRECT to create the reference, based on sheet names in a cell, because INDIRECT doesn't support 3D references.
The SHEET function tells you the sheet number for a reference. In the screen shot below, this formula:
returns a 3, as the number for the Dept02 sheet.
It's the 3rd sheet tab, so you can tell that none of the sheets before it are hidden.
The SHEET function can also return the sheet number for a named range or a named Excel table
In the SHEET function, if you omit the reference, it tells you the number of the active sheet.
In the screen shot below, the result is 5, but the 3rd sheet tab is active. That indicates there are 2 hidden sheets before the active sheet.
The SHEET function takes a regular reference -- not a 3D reference like the SHEETS function. So, you can use INDIRECT with SHEET, to show the sheet numbers in a list of sheet names.
The screen shot below shows a menu sheet, with all the sheet names in the workbook. In the adjacent column, this formula calculates the sheet name.
=SHEET(INDIRECT("'" & $B3 & "'!A1"))
Now, I can sort the list in alphabetical order, or numerical order, and check for hidden sheets.
The SHEET function as promised, but it would be nice to have a function that lets you get information about a sheet, based on its number. Or, perhaps we could use a sheet number to build a reference, just as we can use the row number and column number.
That way, we could refer to the sheet that is -1 or +1 away from the active sheet.
Download the zipped sample SHEET and SHEETS Functions file. The workbook also uses the FORMULATEXT function, to show the formulas in the example cells. These functions only work in Excel 2013 or later, so you'll see errors if you open the file in an earlier version.
Last updated: December 9, 2016 4:34 PM