The Excel SHEET function, new in Excel 2013, returns the sheet number of the referenced sheet based on its position in the file. It can return the sheet number of any worksheet including visible, hidden or very hidden as well as macro, chart and dialog sheets.
SHEET function syntax:
=SHEET(value)
Where value is the name of the sheet or reference you want the sheet number of. The value can include cell references, named ranges or Table names.
You can see it in action in the table below:
Tip: If you enter the SHEET function without a reference e.g. =SHEET() it will return the position of the current sheet.
Use SHEET Function to Return a Sheet Index
Let’s say you have a list of your sheet names in column A and you want to find their position in the workbook. We can use the INDIRECT function to populate the ‘value’ argument by returning the sheet name from column A and appending it to cell reference A1, as shown below:
Sort Sheet Index with INDEX & MATCH Functions
We can then use INDEX & MATCH to return a sorted list of the sheets listed in cells A12:A15 based on their sheet position in cells B12:B15:
If any changes are made to the sheet order, the table above will automatically update.
Sort Sheet Index with SORT Function
Or if you have Office 365 and the new Dynamic Array formulas you can use the SORT function:
Download the Excel Example File
Enter your email address below to download the sample workbook.
