Excel formula: List sheet index numbers

Generic formula

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

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

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.

Author

Dave Bruns

Related formulas

Get sheet name only

The CELL function is used to get the full file name and path: CELL ( "filename" , A1 ) The result looks like this: path [ workbook.xlsm ] sheetname CELL returns this result to the MID function as the text argument. The sheet name begins...

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...

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.