Sheet name changing in cell

[k.tanton]

I have used the formula =MID(CELL("filename"),FIND("]",CELL("filename"))+1,31)
I get the right sheet name but if I go to other sheets and come back the filename has changed to another sheet name. I have scoured my notes and googled it for ages yet still come up empty handed.

I could understand it not working, or failing if I was changing sheet names but I am not...

Is there another way to get the same result that it is a bit more stable than what I already have..... OR, or have I got something wrong in my sheet or code elsewhere that is effecting it?

Thank you to anyone that can help.

[Glenn Kennedy]

Link it to a specific cell:

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

[k.tanton]

I think that seems to have worked.

Thank you very much - I have given you some more reputation.....

I am very new to this so I am learning. Can you tell me why yours has worked yet the one I was using fails please?

thanks in advance

[Glenn Kennedy]

It happens because the cursor must still be focussed on a cell in another sheet. When you return the focus to the sheet with the formula, it will (should!!) show sheet1 again.

It's better to refer to a cell in the active sheet. It will then ALWAYS show Sheet1.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.