List sheet index numbers

List sheet index numbers

Generic formula 

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

Related formulas 

Get sheet name only

Worksheet name exists

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.

List sheet index numbers

Generic formula 

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

Related formulas 

Get sheet name only

Worksheet name exists

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.