The function =CELL("filename",A1)
will return something like > C:\DirectoryName\[WorkBookName.xls]SheetName
which is the full path of the current workbook. Enclosed within [ ] is the WorkBook name
After the ] is the Sheet name
Note: in the function it has A1, this is used to reference the Activesheet (Anchor)
if you didn't have this in and you used it on multiple sheets they would all display the last
Sheet name that was active until you recalculated the Sheet formula
=MID(CELL("filename",A1),FIND("]",CELL("filename"),1)+1,255)
The Find function just returns the position of the 1st instance of the last ]
MID evaluates the string to return the sheet name only