How to Get the Sheet Name of an Excel Worksheet

How to Get the Name of an Excel Worksheet

posted on 12 Feb 15

comments 0

●

Often, you need to display and work with the sheet name, for example if you are working with the ‘INDIRECT’-formula. If you don’t want to type the sheet name manually – which is very unstable – there are three ways to get a sheet name:

1st: Get the sheet name using built-in Excel functions

The easiest way is to copy the following function and paste it into your Excel cell:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))

1	=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))

This formula obtains the filename “=CELL(“filename”,A1)” and separates it after the character “]”. If you want to get the name of another Excel sheet, you have to change the cell reference from “A1” to any cell of the other worksheet. And depending on your version and language of Excel, you might have to translate the function names and maybe replace “,” by “;”.

2nd: Get the sheet name using VBA

The second way to get a worksheet name uses VBA. Once set-up, it might be easier to use. You have to open the developer tools and add a module. Next, you can use this code:

Function PROFEXSheetName(cell As Range) As String
    'This function returns the name of a selected worksheet
    '"cell" contains the link to a cell on the worksheet
    
    Application.Volatile
    'With "Application.Volatile" you can make sure, that the function will be recalculated once the worksheet is recalculated
    
    PROFEXSheetName = cell.Worksheet.Name
    'Returns the worksheet name
    
End Function

Function PROFEXSheetName(cell As Range) As String

'This function returns the name of a selected worksheet

'"cell" contains the link to a cell on the worksheet

Application.Volatile

'With "Application.Volatile" you can make sure, that the function will be recalculated once the worksheet is recalculated

PROFEXSheetName = cell.Worksheet.Name

'Returns the worksheet name

End Function

Now, you can use the function “=PROFEXSheetName(A1)” in your Excel cell and the worksheet name will be shown.

As the result of both ways is (or should be…) the same, it is up to you which way to choose. If you feel comfortable using an easy macro, you probably go for the second way. If not, just paste the formula from the first way to your cell.

3rd: Use ‘Professor Excel Tools’

Because getting the sheet name is a common problem in Excel, it’s included in the Excel add-in ‘Professor Excel Tools’. Just type =PROFEXSheetName(A1) and it’ll return the sheet name. Download the free trial below.

How to Get the Name of an Excel Worksheet

This function is included in our Excel Add-In 'Professor Excel Tools'

Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.

Henrik Schiffner

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.