Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Referencing Worksheet Tabs.

Referencing Worksheet Tabs

Written by Allen Wyatt (last updated December 27, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


10

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

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-20". 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 (6145) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel 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

Applying Bullets from the Keyboard

Most people use the toolbars to apply bullets to paragraphs. If you want to apply them using the keyboard, then you can ...

Discover More

Official Color Names in VBA

When creating macros, you can use the ColorIndex and Color properties to set or determine the colors in a cell. Excel ...

Discover More

Excel Opens Additional, Unwanted Files

If you open a workbook and Excel decides to open additional, unwanted workbooks, it can be bothersome to try to track ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Jumping to Alphabetic Worksheets

Got a workbook with a lot of worksheets in it? Here are some handy ways to jump to the worksheet you want, alphabetically.

Discover More

Unbreakable Formula References to Worksheets

Excel allows you, in your formulas, to include references to cells on other worksheets. Those references include the name ...

Discover More

Properties for Worksheets

Excel keeps a full set of properties related to workbooks. When it comes to worksheets, however, there is very little ...

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 six minus 4?

2023-12-29 10:23:38

J. Woolley

My Excel Toolbox includes the following function to return information about Target (a cell or range); default Target is the formula's cell:
    =NameOf([This],[Target])
The first parameter This can be "sheet" (or "worksheet"), "book" (or "workbook"), "path" (or "filepath"), "app" (or "application"), "caption" (or "titlebar"), "statusbar", "user", "organization", "printer", "computer", "?" (or "help"), or the name of an environment variable (like "TEMP"). Default value is "sheet" (or "worksheet"); therefore, the following formula will return the name of the worksheet's tab:
    =NameOf()
This function is similar to Excel's CELL and INFO functions, but perhaps more useful. In particular, CELL("filename") is blank ("") if the workbook is new and unsaved. (See the Tip's caveat.) And CELL("filename") returns the "wrong" result if another open workbook is active. NameOf() does not have these issues. See https://sites.google.com/view/MyExcelToolbox/
For related discussion, see https://excelribbon.tips.net/T013432_Inserting_the_Workbook_Name.html


2023-12-28 04:54:59

Mike H

This can now be more simply done by
=TEXTAFTER(CELL("filename"),"]")


2020-03-27 19:11:56

David A Czuba

Modify Alan's formula slightly to get the whole tab name, no matter how long it is:

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

The mod subtracts the position of the square bracket from the length of the filename string, giving the length of the worksheet tab name.


2020-03-27 14:00:07

Jim

I use this formula to put the worksheet name in A1.

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


2020-03-27 09:51:13

Rene

There is an easier way to use the tab name, by using the "indirect" function.
E.g. On Sheet1, cells A1-A3, type in a1, a2, a3. In cells B1-B3 type in the name of the sheet you want to use, whether it is a date (ensure the cell is formatted as text) or other text. In cells c1-c3 use the formula: =indirect(B1&"!"&A1,True). The result will be the value in the cell on the target sheet.

Regards, Rene


2015-06-01 13:42:46

Curt

One can use this function/macro to get the sheet name into a cell:

Function shName(rng As Range) As String
Application.Volatile
shName = rng.Worksheet.Name
End Function

... and then put this formula into the desired cell:

=shname(A1)


2015-06-01 05:25:07

DaveS

@Mark
Further to MA's comment, if you omit the ',10' you'll get a 'too few arguments' error.

The value of 10 is not a mistake given the caveats in the article ("If the names of your worksheet tabs consist only of dates (no other text in them)" and "Dates can be expressed in a maximum of 10 characters, as in 12-31-2011."). But I think "extracts the first eight characters" should read "extracts the first ten characters". And if someone uses a format like dd-mmm-yyyy they'll need 11 characters. Using ',31' does make the function generic. The inclusion of $A$1 reference in the CELL function is critical, otherwise the formula will return whichever tab name was most recently changed.


2015-05-31 06:17:30

Michael (Micky) Avidan

@Mark,
Did you managed to use the MID function leaving out the "Num_chars" argument - which, to the best of my knowledge is
not(!) optional.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2015-05-30 10:57:40

Mark

Just leave out the ",10" and then it will work for any length name with no margin for error.


2015-05-30 10:07:10

Michael (Micky) Avidan

@@@ To whom it may concern and with all doe respect - I have 2 meaning full comments:
1) The value of 10 (in the MID function) is a mistake, especially if the sheets name exceeds 10 characters(*** by fact it can be up to 31 characters).
2) The lack of the reference to $A$1 will make Excel to misinterpret the formula when copied to other sheets.
Try to enter this tips formula in sheet1 (in cell D5) > copy it and paste in it Sheet2 cell D5.
Check, now, the results, of the formulas, in the two sheets.
So, my suggested formula will look like that:

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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.