|| Links | Alphabetical Index | Cell references and the INDIRECT function ||
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.
|32||Formula||[B31] =CELL("filename", A1)|
|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.
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
MySheetName = "MiK ERROR: invalid sheet number"
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file: worksheet-names.htm||Page last updated: Feb14||© MeadInKent.co.uk 2014||CMIDX S6 P4 Y|