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))