Get a Sheet Name using a named formula and get every Sheet/Tab name not the same name

[drom]

Hi and thanks in advance!

if I put this formula in my activhesheet

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

I will get my activesheet name

If I create a name named "MyName" when my Activesheet is "France" like:

MyName =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))

Excel converts MyName

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))​

to

=MID(CELL("filename",France!A1),FIND("]",CELL("filename",France!A1))​

So in every Tab I get the shame answer when I put =MyName

France​

So is there any solution to avoid this?

If I am in a Sheet Named UKI and I put =MyName get UKI​

If I am in a Sheet Named Ireland and I put =MyName get Ireland​

If I am in a Sheet Named NESA and I put =MyName get NESA​

If I am in a Sheet Named Portugal and I put =MyName get Portugal​

Why I do not like to put in A1 = MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) instead of =MyName

Because I am trying to know if is possible to create a dinamic formula to get a sheets Name using the same named formula

 

[StephenCrump]

MyName: =MID(CELL("filename",!$A$1),FIND("]",CELL("filename",!$A$1))+1,255)