Deriving the Worksheet Name

by Allen Wyatt
(last updated November 2, 2013)

There may be instances when you are developing a worksheet and you need to reference the name that you have assigned to the worksheet. (This is the name that appears on the worksheet tab, at the bottom of the Excel window.) Unfortunately, Excel does not include any intrinsic functions to do this. You can create such a function, however, by starting with the use of the CELL worksheet function.

If you include the following in a cell, Excel returns the fully path of the workbook, along with the sheet name:

=CELL("filename")

For instance, if you entered this into a cell in the Sheet3 worksheet of the MyBook workbook, the information returned by Excel might be something like C:\My Documents\[MyBook.xls]Sheet3 (depending, of course, on the drive and directory in which the workbook is saved).

To return just the worksheet name from this value, you could use the following in your cell:

=MID(CELL("filename"),(FIND("]",CELL("filename"))+1),50)

This will work for any worksheet name up to 50 characters in length. (If you routinely use different lengths, simply change the value in the expression.)

If you would prefer to use a macro-oriented approach, you can create a full-featured macro that will do the job. The following macro, SheetStuff, will return any of three separate items:

Function SheetStuff(numWanted As Byte) As String
    Select Case numWanted
        Case 1
            SheetStuff = ActiveSheet.Name
        Case 2
            SheetStuff = ThisWorkbook.Name
        Case 3
            SheetStuff = ThisWorkbook.FullName
        Case Else
            SheetStuff = ActiveSheet.Name
    End Select
End Function

To use this macro function, simply put =SheetStuff(X) in a cell in your worksheet. You should replace X with either 1, 2, or 3, depending on the information you want. If you use 1, the name of the current worksheet is returned. If you use 2, then the name of the workbook is returned. Finally, 3 returns the name and full path of the workbook.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2019) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Shortcut to Merge Cells

Need to merge a bunch of cells together on a regular basis? You'll love the two macros in this tip which can make short work ...

Discover More

Replacing a Colon in a Sequence

Sometimes you'll run across the need to replace a very specific sequence of characters in your document. It is for these ...

Discover More

Using Named Formulas Across Workbooks

You can use the naming capabilities of Excel to name both ranges and formulas. Accessing that named information in a workbook ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (MENU)

Creating Add-Ins

Want to create your own add-in? Excel makes it easy to do. Here are all the steps you need.

Discover More

Fixing Macro Button Behavior in Protected Worksheets

When working with macro buttons, you may run into some bizarre behavior related to the macros without really understanding ...

Discover More

DOS From Macros

Need to run a DOS command from within one of your macros? The answer is the Shell command, described in this tip.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share