How to get the SheetName from =SheetName!A1

[Ceeyee]

HI,

In Cell D1 I have a formula =AAA!A1
AAA is a Sheet Name of another sheet

How to use a formula referencing this cell D1 to return AAA?
i.e Formula(D1) returns AAA

Thanks.

 

[dispelthemyth]

TRY THIS FORMULA

=MID(CELL("filename",AAA!A1),SEARCH("]",CELL("filename",AAA!A1),1)+1,LEN(CELL("filename",AAA!A1)))

 

[niceguy_jay]

you will need to use this formula in the same sheet whose name you want to extract

=MID(CELL("filename"),SEARCH("]",CELL("filename"),1)+1,LEN(CELL("filename")))

 

[FishingIsMoreFun]

Here is also a function, works for me

cheers

Function trimformula(targetCell As Range)

Dim X  As String
Dim Y  As String
Dim A As Integer
Dim B As Variant

   X = InStr(1, targetCell.FormulaLocal, "(", vbTextCompare)
   Y = InStr(1, targetCell.FormulaLocal, "!", vbTextCompare)
   
   A = (Y - X)
   B = Mid(targetCell.FormulaLocal, X + 1, A - 1)
   
   trimformula = B
   
End Function

 

[FishingIsMoreFun]

HI,

In Cell D1 I have a formula =AAA!A1
AAA is a Sheet Name of another sheet

Am I out of date here or from what I understand =AAA!A1 is not Excel conventional sheet naming within a formula nor is it a formula?
Anyway you can edit the Function above just replace the "(" with an "=".

 

[Ceeyee]

It works! Thanks.

Am I out of date here or from what I understand =AAA!A1 is not Excel conventional sheet naming within a formula nor is it a formula?
Anyway you can edit the Function above just replace the "(" with an "=".

 

[Ceeyee]

Thank you.

you will need to use this formula in the same sheet whose name you want to extract

=MID(CELL("filename"),SEARCH("]",CELL("filename"),1)+1,LEN(CELL("filename")))