Home	Excel	Access
| Links | Books | 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 gives the name of a worksheet. It is possible to manipulate the CELL() function to return the worksheet name of a cell but this requires you to define the location in the first place.

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).

Sometimes however you may wish to obtain 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 table, the values in 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, thereby reading a value from each page.

- 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))

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.

This new page is not currently included in the Excel Functions guide

Click here for details about obtaining this file