This blog post looks at using an Excel formula to display the sheet name in a cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet name.
For the formula we will be using the CELL, MID and FIND functions. Let’s begin by looking at the CELL function.
The CELL function is a fantastic, and relatively unknown, function in Excel. Its purpose is to return information about a cell such as its column, cell colour, whether it is in a protected state or the filename.We need to use the function to return the filename of a cell. Because our goal is to return the sheet name, it does not matter what cell we use. Any cell on the sheet will work. In the example below, cell B1 has been used.
=CELL("filename",B1)
The function above will return the full filename of the cell such as;
C:\Users\Trainee1\Desktop\[return-sheet-name.xlsx]London
Extracting Just the Sheet Name
Now that we have the filename, we need to extract just the sheet name. The MID and FIND functions will be used for this.
The FIND function is used to return the position of the closing “]” + 1. The “]” indicates the end of the workbook reference, so the following characters are those for the sheet name.
The MID function then extracts up to the next 32 characters. This is an excessive number but because sheet names cannot have more than 31 characters it ensures the full name is returned whatever it may be.
Here is the full Excel formula to display the sheet name in a cell;
=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,32)
Watch the Video
Great function. I can get staff members to now remember where they put their files by this function. Thanks
Is there any way to reverse engineer this, so the sheet name is determined by a cell value?
This can only be done with a macro using a statement such as Activesheet.Name = Range(“A1”).Value
Sorry, but the filename (workbook name) is not the same as the sheet name inside a workbook.
“A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.”
I know, Brian. The formula returns the filename and extracts the sheet name from it.
Note :- Formula Work only if your Sheet Name in Numeric
=”=’E:\Contacts\”&”[“&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”‘!$B$1048576″
Use this Formula To Get Value from other Excel Sheet
________________
Define Path = “=’E:\Contacts\”&”[”
Give Sheet Name [if Sheet Name in Numericl] = SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1
Cell No. = !$B$1048576
Is there a difference between Excel in Windows and Excel on Macbook?
Yes, there are differences between the two. Not in formulas but in other areas especially VBA and Power Query.