

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:
Contents
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 “;”.
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
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.
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.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.