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!'