Excel formula: List sheet index numbers

Generic formula

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

Explanation

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"))

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.

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

Author

Dave Bruns

Related formulas

Get sheet name only

If you want to get the sheet name only (i.e. the sheet name without the file name or path) you can do so with rather long formula that uses the MID function along with the FIND function. The final result will look something like this: Sheet1 How the...

Worksheet name exists

To test if a worksheet name exists in a workbook, you can use a formula based on the ISREF and INDIRECT functions. In the example shown, the formula in C5 is: = ISREF ( INDIRECT ( B5 & "!A1" )) How this formula works The ISREF function...

Related functions

Excel SHEET Function

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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.