Exceljet

Quick, clean, and to the point

List sheet names with formula

Excel formula: List sheet names with formula
Generic formula 
=GET.WORKBOOK(1)&T(NOW())
Explanation 

To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called "sheetnames" with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range. In the example shown, the formula in B5 is:

=INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),ROWS($B$5:B5))

Note: I ran into this formula on the MrExcel message board in a post by T. Valko.

How this formula works

The named range "sheetnames" is created with this code:

=GET.WORKBOOK(1)&T(NOW())

GET.WORKBOOK is a macro command that retrieves an array of sheet names in the current workbook. The resulting array looks like this:

{"[workbook.xlsm]Sheet1","[workbook.xlsm]Sheet2","[workbook.xlsm]Sheet3","[workbook.xlsm]Sheet4","[workbook.xlsm]Sheet5"}

A cryptic expression is concatenated to the result:

&T(NOW())

The purpose of this code is to force recalculation to pick up changes to sheet names. Because NOW is a volatile function, it recalculates with every worksheet change. The NOW function returns a numeric value representing date and time. The T function returns an empty string ("") for numeric values, so the concatenation has no effect on values.

Back on the worksheet, cell B6 contains this formula copied down:

=INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),ROWS($B$5:B5))

Working from the inside out, the MID function is used to remove the worksheet names. The resulting array looks like this:

{"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}

This goes into the INDEX function as "array". The ROW function uses an an expanding ranges to generate an incrementing row number. At each new row, INDEX returns the next array value. When there are no more sheet names to output, the formula will return a #REF error.

Note: because this formula relies on a macro command, you'll need to save as a macro-enabled workbook if you want the formula to continue to update sheet names after the file is closed and re-opened. If you save as a normal worksheet, the sheetname code will be stripped.

Author 
Dave Bruns

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.