In general, the formula to display the sheet name is:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

(BTW the Workbook must have been saved for this to work correctly)
To extract the Sheet name from a formula, write a short Function 

Function CellFormula(c) As String
If c.HasFormula Then
CellFormula = c.Formula
Else
CellFormula = ""
End If
End Function

You can then use the following formula to display the Sheet name referenced by a formula 9Such as “Sheet1!A2”

=MID(LEFT(Cellformula(A2),FIND(“!”,Cellformula(A2))-1),2,255)

or something like

=IF(ISERROR(MID(LEFT(cellformula(L2),FIND("!",cellformula(L2))-1),2,255)),"No sheet is referenced",MID(LEFT(cellformula(L2),FIND("!",cellformula(L2))-1),2,255))