Returning the Name of the Sheet:

=CELL("Filename",A1) returns the complete file path and sheet name

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) will strip away everything but the sheet name.
Note: The file must be saved or the formula will not work.