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

Written by Allen Wyatt (last updated April 3, 2024)**This tip applies to** Excel 2007, 2010, and 2013

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

**Professional Development Guidance!** Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out *Professional Excel Development* today!

Microsoft added a new feature to Excel that causes a "lock icon" to appear at the left of a worksheet tab if the ...

Discover MoreAs you get more and more worksheets into a workbook, you'll find yourself moving them around into different sequences. ...

Discover MoreExcel, by default, recalculates your worksheets as you make changes in those worksheets. If you want to limit the number ...

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

2024-04-04 14:25:42

J. Woolley

=NameOfSheet([Position], [Absolute])

Position is the location of the sheet in the workbook or 0 (default) for the current worksheet.

If Absolute is FALSE (default), Position is relative to the current worksheet; therefore, NameOfSheet(-1) refers to the sheet on the left.

If Absolute is TRUE, Position is the absolute location of the sheet in the workbook (base-1); therefore, NameOfSheet(3,TRUE) refers to the 3rd sheet.

Notice Excel's SHEET function returns the absolute position of a sheet, and the SHEETS function returns the number of sheets (including hidden or very hidden).

My Excel Toolbox also 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, NameOf() will return the name of the formula cell's worksheet.

The NameOf 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.

Both NameOfSheet and NameOf are Volatile functions; therefore, they will update if a name is changed. (See JMG's comment below.)

See https://sites.google.com/view/MyExcelToolbox/

2024-04-04 00:03:40

Philip

Note that the function GET.CELL can NOT be called directly in the worksheet, only through the Name Manager. It does allow to get dozens of "attributes" or "properties" of a cell .... a hidden gem

2024-04-03 04:09:56

Mike H

=TEXTAFTER(CELL("filename"),"]")

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 © 2024 Sharon Parq Associates, Inc.

## Comments