A to Z of Excel Functions: The SHEET function

The SHEET function

Aw, SHEET…  Arriving in Excel 2013, the SHEET function in Excel returns the sheet number of the reference sheet.  This is cited as the ordinal position of the referenced sheet, including any hidden, very hidden, macro, chart or dialog sheets (i.e. if all sheets were unhidden what its position would be in the tab order).

This sheet number is not the same as the one resulting from its VBA counterpart, where the value is based upon the order of creating, adding or copying that sheet to the workbook.

The SHEET function employs the following syntax to operate:

The SHEET function has the following argument:

• value: this is optional and represents the name of a sheet or a reference for which you want the sheet number.  If value is omitted, SHEET returns the number of the sheet that contains the function.

It should be noted that:

• SHEET includes all worksheets (visible, hidden or very hidden) in addition to all other sheet types (macro, chart or dialog sheets)
• if the value argument is not a valid value, SHEET returns the #REF! error value.  For example, =SHEET(Sheet1!#REF) will return the #REF! error value
• if the value argument is a sheet name that is not valid, SHEET returns the #N/A error value.  For example, =SHEET(“BadSheetName”) will return the #N/A error value
• if the value argument contains a defined name which is limited to the worksheet that contains it, SHEET will return the #NAME? error
• SHEET is not available in the Object Model (OM) because the Object Model already includes similar functionality.

Please see my examples below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.