To do that, there is a way: Define a name, like SheetName, and place in the "Refers to:" field, this formula:
=GET.CELL(62;Sheet2!$G$26) (if you want another sheet, change the reference in red)
Then, in any cell in any sheet, you will get the sheet2 name with:
=RIGHT(SheetName;LEN(SheetName)-FIND("]";SheetName))
You can simply use:
=SheetName
but you will get the sheet name precedeed by the workbook name...
For a list of information that can be obtained from Get.Cell function, (the function is available only in defined names, or in VB), you can check this:
http://www.mrexcel.com/forum/excel-q...arguments.html

Or, another way, is to use Cell function, available in sheet functions:
=RIGHT(CELL("filename";J16);LEN(CELL("filename";J1 6))-FIND("]";CELL("filename";J16)))
The =CELL("filename";J16) formula extracts the full path of the reference...