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;
Where A1 is any non error cell on the Worksheet. If you want the full path of the Excel Workbook, simply use;
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,FALSE) OR =SheetName(A1)
To have the Worksheet name returned as Sheet1
|Excel: Remove Duplicates in Excel|
|Restricting Excel VBA Loops|
|Return Excel Color Index Number or Color as Text|