Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Referencing Worksheet Tabs.

Referencing Worksheet Tabs

by Allen Wyatt
(last updated June 23, 2018)

2

Myrna asked if there was a way to use the information in a worksheet tab within a cell. In particular, she named her tabs using dates, and wants to use those dates within the worksheet itself.

There are two ways to go about this. If the names of your worksheet tabs consist only of dates (no other text in them), then you can use the following Excel formula to extract the date:

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

This works because =CELL("filename") function returns the complete path and name of the current file along with the text on the worksheet tab. The filename itself appears in square brackets. The formula finds the position of the closing bracket and extracts the first eight characters from that position to the end. (Dates can be expressed in a maximum of 10 characters, as in 12-31-2011.)

One caveat with using this formula is that it only returns anything of value if you first save the workbook. If you use it in a brand new, unsaved workbook, it will return a #VALUE error.

Another approach that is very appealing, particularly if you have additional text in the worksheet tab, is to create a user-defined function. For instance, let's assume that your worksheet tabs have the name "Month Ending 10-31-11". In this case, you could use a function such as the following:

Function SheetName() As Date
    Dim sTab As String
    Application.Volatile
    sTab = ActiveSheet.Name
    sTab = Trim(Right(sTab, 8))
    SheetName = CDate(sTab)
End Function

To use this function in your worksheet, you simply enter the following in a cell:

=SheetName()

The function returns a date serial number, so you will need to format the cell using one of the available date formats. The function works because it assumes that the date is the last 8 characters of the text in the worksheet tab. If your worksheet tabs use a different naming convention (such as placing the date at the beginning of the tab or using 10 digits for the date), then all you need to do is pull the name apart differently in the macro.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1951) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Referencing Worksheet Tabs.

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

Turning E-mail Addresses into Hyperlinks

Got a whole bunch of e-mail addresses that you need to convert to active hyperlinks? You can do the conversion in a ...

Discover More

Importing Custom Lists

Custom lists are handy ways to enter recurring data in a worksheet. Here's how you can import your own custom lists from ...

Discover More

Determining Font Formatting

If you need to determine the font applied to a particular cell, you'll need to use a macro. This tip presents several ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (menu)

Freezing Worksheet Tabs

If you have a lot of worksheets in a workbook, you may wonder if you can "freeze" the position of some of those worksheet ...

Discover More

Naming Tabs for Weeks

Need to set up a workbook that includes a worksheet for each week of the year? Here's a couple of quick macros that can ...

Discover More

Changing the Height of Worksheet Tabs

Do you need your worksheet tabs to be taller than what they are? You can't make the adjustment in Excel, but you can make ...

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

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 6?

2021-01-20 16:41:33

Tim

Hi Allen - Thank you for this, it looks to be very helpful with what I am doing... except I have a bit of an issue. I have my personal budget in excel - each tab as a chronological list of expenses in a given month, the tabs being named that month. At the top of each tab is the first of that month and the following expenses use that date to calculate their due date. I copy and pasted the string into VBA according to your direction, and have changed the first date in each tab to be =sheetname(), but now the first line in all tabs is whatever the last calculated date was regardless of the tab name it resides.
For example, I click on the first item in the 'Jul 2021' tab and it populates 7/1/21 (perfect)... but it also changed the top lines in all tabs to 7/1/21, not following the name of the individual tabs. What did I do wrong?? Thanks in advance!


2017-12-30 05:04:31

gerdami

I often use this tip to duplicate database queries located on one worksheet per country. The sheetname is the ISO 3166-1 alpha-2 country code that is also used as part of the seriescode. I just copy the worsheet. I rename the sheetname to e.g. "US", and this country code is automatically replaced in all database queries located on that new sheet.


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.

Newest Tips
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.