An Excel Function to return the names of worksheets

There isn't a 'built-in' function which only gives the name of each worksheet. The CELL() function returns the spreadsheet filename, worksheet name and the cell reference, but not a simple list of worksheet names. The CELL() function must be supplied with a specific cell location.

The final cell reference argument is optional but if it is missed the worksheet name supplied by this function may refer to a previously selected worksheet if no activity has occured on the current sheet.

It is possible to use the MID() and FIND() functions to strip off the location and filename, so that only the Worksheet name ('MyIndex') remains (in cell B33).

Visual basic can be used to create a list of all of the sheet names without using CELL() to individually select a cell on each one. In a large Excel spreadsheet containing lots of worksheets, it may be useful to build an index list - possibly to read the values from a particular cell reference on each sheet.

In the following example, the values in the range B4:B7 are obtained using visual basic function called MySheetName() which returns the name of worksheet based on its sequence number (i.e. 1-4). If the sequence number exceeds the number of worksheets in a spreadsheet, an error message is returned. The cells D4:D7 use the INDIRECT() function to create a string name of a cell reference, and thereby read a value from each sheet.

- A B C D 2 Index of worksheets     3   sheet name cell contents 4 1 MyIndex A2 Index of worksheets 5 2 Main A1 fish 6 3 Table A1 chips 7 4 MiK ERROR: invalid sheet number a1 ***** 8     www.meadinkent.co.uk
where [B4] =MySheetName(A4) and [D4] =IF(LEFT(B4, 4)="MiK ", "*****", INDIRECT("'" & B4 & "'!" & C4, TRUE))

The INDIRECT part of the formula includes extra apostrophes enclosing the B4 reference. This is necessary in case the worksheet name includes spaces between words.

Note that this VB user defined function will not automatically adjust to reflect any changes made to sheet names. It is necessary to manually recalculate (by pressing <F9>).

The visual basic code for a user defined function is shown below.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.