**Please Note: **
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 a Worksheet Name.

Jon wonders if there is a function equivalent to =ROW() or =COLUMN() for worksheets. He needs to reference (for example) the fourth sheet in a workbook, but he can't be sure of the worksheet's name.

There are a couple of ways to approach this problem, depending on what you need to do. If you are working with a worksheet that has already been saved, then the following formula will provide you with the worksheet name for Sheet4:

=MID(CELL("filename",Sheet4!A1),FIND("]",CELL( "filename",Sheet4!A1))+1,LEN(CELL("filename", Sheet4!A1)))

You should note that there are couple of assumptions in this formula. First (and most importantly) it assumes that you know the initial name of the worksheet. In this case, the initial name is Sheet4. After the formula is in place, subsequent changes to the worksheet name will be reflected automatically in the formula. The second assumption is that the workbook you are working in has been saved. If it hasn't, then the formula returns an error until the workbook is saved and recalculated.

A different approach is to use a user-defined function. In VBA's object model, all the worksheets in a workbook are contained within the Sheets collection. These are, in turn, indexed. Thus, you can pass an index value to the function and get back the name of the worksheet at the collection's index number.

Function TabName(lSNum As Long) As String If lSNum > 0 And lSNum <= Sheets.Count Then TabName = Sheets(lSNum).Name End If End Function

For instance, if you wanted to know the name of the fourth worksheet in the collection, you could use the following in your worksheet:

=TabName(4)

The function will work just fine, even in a workbook that has not been saved. It also returns the proper worksheet name even if the worksheets are renamed or moved around.

*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 (11553) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: **Referencing a Worksheet Name**.

**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!

Excel makes it easy to change the color of a worksheet's tab. If you want that color change to be dynamic, one way to do ...

Discover MoreWhen processing workbook information in a macro, you may need to step through each worksheet to make some sort of ...

Discover MoreWant to make fast work of moving from one worksheet to another? Here's how to do the task when you have a lot of ...

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

2017-11-16 11:54:52

JMG

2017-07-10 17:19:33

Craig T

So, if I get it to return Sheet1 in A1, can the formula be modified to return Sheet2 in A2, and so on.

2016-11-30 11:21:32

Gary Lundblad

When I copy your user defined function code into a module in my personal workbook, so it can be used with any open workbook, and then try to call that function in another workbook, I simply get a =NAME? error. If I copy the code into the workbook that I'm trying to use it in, it works fine. Is there a way the function can work in any open workbook?

Thank you!

Gary

2016-11-15 19:49:58

Neil

RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))

when placed on the worksheet to get the name of.

2016-11-15 10:15:36

Neil M Gibson

2016-11-15 08:29:03

Jim Stevens

2016-01-29 12:01:24

Hal

2014-12-15 23:30:29

sunil kumar gupta

Please guide me how to creat index sheet with the help of buttons in Balace sheet

2014-12-05 10:42:48

KC

In line with Barry's recommendation on using CodeName, I am trying to use it to reference from a hyperlink, rather than relying on the sheet names or index. Is there a way to do that?

Thank you

KC

2013-09-19 23:34:58

John

Thank you for taking the time to answer my question. Your lengthy, all-in-one formula does exactly what I was hoping to accomplish. I enjoy reading your contributions here, especially now that I'm a direct beneficiary.

2013-09-18 10:16:09

Bryan

Then for your hyperlink, use the following formula:

=HYPERLINK("[" & FileName & "]" & SheetRef & "!A1",SheetRef)

When you change the name of the referenced sheet, both the hyperlink and the link text will update accordingly. I'm sure there are ways to make the formulas involved more elegant, but my brain hurts right now and I know these will work regardless :)

* There's another tip about it here somewhere, but you can find this with the following formula if you need it to be dynamic:

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

If you want to put everything into one cell, the formula simply becomes much longer:

=HYPERLINK("[" & MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) & "]" & MID(CELL("filename",Test3!A1),FIND("]",CELL("filename",Test3!A1))+1,LEN(CELL("filename",Test3!A1))) & "!A1",MID(CELL("filename",Test3!A1),FIND("]",CELL("filename",Test3!A1))+1,LEN(CELL("filename",Test3!A1))))

(Where "Test3" was the name of the sheet in question)

2013-09-18 09:32:08

John

The first example provided in this tip happens to use a default sheet name with an index number, but that's incidental. Enter the formula as given, then change the name of Sheet4 to Travel Expenses, for example. The formula you entered will be updated to read

=MID(CELL("filename",'Travel Expenses'!A1),FIND("]",CELL("filename",'Travel Expenses'!A1))+1,LEN(CELL("filename",'Travel Expenses'!A1)))

Starting out with the standardized default names simplifies replicating the formula to generate a list of all the sheet names, a list that will automatically update as you rename sheets.

2013-09-18 05:19:28

Barry Fitzpatrick

A better method is to use the worksheets 'code name'.

2013-09-17 09:08:27

John

But hyperlinks to other sheets are always stymied when I rename a tab. Is there some way to get them to learn from the CELL function? (Some way that doesn't involve a macro since Excel 2008 for Mac doesn't support VBA.)

As an aside, the MID function doesn't need to calculate the length of the filename when capturing everything to the right. The LEN function can be replaced with a large constant such as 999.

2013-09-16 09:00:21

Bryan

I can't figure out what this would be used for.

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.

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

Copyright © 2020 Sharon Parq Associates, Inc.

## Comments