MeadInKent
| Links | Alphabetical Index | Cell references and the INDIRECT function |

An Excel Function to return the names of worksheets

There isn't a 'built-in' function which only gives the name of a 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.

- A B
31 Result D:\Excel\[Excel GetSheetNames.xls]MyIndex
32 Formula [B31] =CELL("filename", A1)
33 Result MyIndex
34 Formula [B33] =MID(B31, FIND(".xls]", B31, 1)+5, 50)

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
Excel worksheet tabs containing the names of each page

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.

Function MySheetName(SheetNumber As Integer) As String

If SheetNumber <= Worksheets.Count Then
 MySheetName = Worksheets(SheetNumber).Name
Else
 MySheetName = "MiK ERROR: invalid sheet number"
End If

End Function


excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: worksheet-names.htm Page last updated: Feb14 MeadInKent.co.uk 2014CMIDX S6 P4 Y