List worksheet index numbers in Excel
To list the index numbers of sheets in an Excel workbook, you can enter the sheet names, then use a formula based on the SHEET and INDIRECT functions.
The SHEET function then returns the current index for each sheet as listed.
In the example shown, the formula in C5 is:
How this formula works
The INDIRECT function tries to evaluate text as a valid reference. In this case, the sheet name is pulled from column B and concatenated with an exclamation point and the text A1:
=B5&"!A1" ="Sheet1"&"!A1" ="Sheet1!A1"
The INDIRECT function then coerces the text “Sheet1!A1” into a valid reference, which is passed into the SHEET function.