List worksheet index numbers in Excel

by

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.

Worked Example:   How to create dynamic worksheet reference in Excel

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

Formula

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

Explanation

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.

Worked Example:   Sum bottom n values in Excel

 

Leave a Reply

Your email address will not be published.