How to Get the Name of an Excel Worksheet
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)))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 FunctionNow, 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.
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.
