Formula to return tab name

[pholt33]

Help with formula to return tab name

I am trying to come up with a formula that returns the sheet name on which the formula resides. How do I do that? So far Ive used =CELL("filename"), but that returns the entire reference string.

Thanks!

 

[lenze]

Re: Help with formula to return tab name

Several good formula examples can be found here http://www.mrexcel.com/forum/showthread.php?t=304968&highlight=Sheet+Name

I personally, would use code (example also at that link)

lenze

 

[Richard Schollar]

Re: Help with formula to return tab name

Hi

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

 

[pholt33]

Re: Help with formula to return tab name

Hi

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

That worked perfectly! Thanks!

Just to help me understand what its doing, can you explain in English how that formula works?

 

[Richard Schollar]

Re: Help with formula to return tab name

Sure:

Cell("filename",a1) returns the full path & workbook & worksheet name of the worksheet in which it is entered. The format of this value means that the workbook name is always surrounded by square brackets ie [] - so if we find the square bracket ] we know that anything after this bracket is the sheet name. So, we replace all the characters from the 1st up to and including the position of the square bracket with an empty string "" (ie we effectively delete the superfluous data) which just leaves us with the worksheet name.

 

[Zack Barresse]

Re: Help with formula to return tab name

Another good method is using the add-in ASAP Utilities (www.asap-utilities.com) and using the function =SHEETNAME(). Another way to skin the cat I guess.