Hi,

Is it alright for you to use a VBA custom function which works in an exactly the same way. It is definitely possible to return what you are asking for. Here is a formula that would return the sheet name in the sheet it is used.
=RIGHT(CELL("filename",B1),LEN(CELL("filename",B1) )-FIND("]",CELL("filename",B1),1))

As you see it is a cumbersome process to get such a simple information from built in functions. Also looking at the existing way the formula is return there can be some issues later such as what happens when someone inserts a new sheet. You will have to modify your formula.

I would suggest a VBA function for such a task but want to check with you if you are happy with this before I can create a custom function for you.

Let me know your thoughts.

Prashant