Return an Excel Worksheet/Sheet Name to a Cell
Category: [Excel] Demo Available
Return an Excel Worksheet/Sheet Name to a Cell
How to Return an Excel Worksheet/Sheet Name to a Cell via VBA & Custom Function/Formula
Got any Excel Questions? Free Excel Help
Return an Excel Worksheet Name to a Cell
In Excel it is possible to use the CELL function/formula and the MID and FIND to return the name of an Excel Worksheet in a Workbook. The formula below shows us how;
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
Where A1 is any non error cell on the Worksheet. If you want the full path of the Excel Workbook, simply use;
=CELL("filename",A1)
It is important to note that the above formulas will only work in a Workbook that has been saved.
Return an Excel Worksheet Name to a Cell VBA
We also use an Excel VBA custom function (user defined function) to return the name of an Excel Worksheet. The big advantage to using a custom VBA function is that we can opt to have the Worksheet name surrounded by both ' and '! e.g 'Sheet1'! This then means we can use the cell as a reference in a formula/function like the INDIRECT function.
Function SheetName(rCell As Range, Optional UseAsRef As Boolean) As String
Application.Volatile
If UseAsRef = True Then
SheetName = "'" & rCell.Parent.Name & "'!"
Else
SheetName = rCell.Parent.Name
End If
End Function
To add the code, open the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11) then go to Insert>Module and paste in the code above. Come back into Excel proper and use like shown below to have the Worksheet name returned as 'Sheet1!'
=SheetName(A1,TRUE)
Or as
=SheetName(A1,FALSE) OR =SheetName(A1)
To have the Worksheet name returned as Sheet1
See also:
| Excel: Remove Duplicates in Excel |
| Restricting Excel VBA Loops |
| Return Excel Color Index Number or Color as Text |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.