Getting the name of your worksheet into a cell - without using VBA!

VBAHave you ever needed to put the name of a worksheet into a cell? Of course, you can just type in the name. What if, however, you have 250 sheets with different names? That is an awful lot of typing. Formulas to the rescue! Next time, try this neat little trick...

Create a new workbook and save it with some name, for example test.xls. Then, create a nice name for your worksheet. Now, here's the magic formula:

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

Hm.... does it work?

Apparently it does!

So... what's the magic?

The function "CELL" returns information about a given cell, here we have asked for the filename - this one includes the sheet name as well:

hdisk:Users:username:path:[test.xls]My Fantastic Sheet Name

Now, the function "MID" returns the internal text starting from a certain character number and going on until a finish number. Here, the number we start at is the one after the "]" sign in the text string returned from cell. This number is found using the "FIND" function. 

Pretty nifty, eh? I'm sure this trick has saved me many hours of typing over the years!