Summary

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. In the example shown, the formula in C5 is:

=SHEET(INDIRECT(B5&"!A1"))

Generic formula

=SHEET(INDIRECT(name&"!A1"))

Explanation 

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.

The SHEET function then returns the current index for each sheet as listed.

Related formulas

Excel formula: Get sheet name only

Get sheet name only

In this example, the goal is to return the name of the current worksheet (i.e. tab) in the current workbook with a formula. This is a simple problem in the latest version of Excel, which provides the TEXTAFTER function . In older versions of Excel, you can use an alternative formula based on the...
Excel formula: Worksheet name exists

Worksheet name exists

The ISREF function returns TRUE for a valid worksheet reference and FALSE is not. In this case, we want to find out of a particular sheet exists in a workbook, so we construct a full reference by concatenating the sheet names in column B with an exclamation mark and "A1": B5...

Related functions

Excel SHEET function

SHEET Function

The Excel SHEET function returns the index number of a sheet in Excel. SHEET will report the sheet number for a cell reference, named range, or Excel Table.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions