Display worksheet name in cell a1 1

[Sareimer]

Hello All,

I'm trying to display my worksheet name in cell A1. I checked the FAQs the but the one listed on this subject isnt working for me. Any help? I would like to use a formula vs VBA.

Thanks,
Steph

 

[dan1967]

There is no standard function for this. You can create your own and attach it to your worksheet. Should be pretty simple:

Function ShtName()
ShtName = ActiveSheet.Name
End Function

Dan.

 

[GlennUK]

Excuse me Dan1967, but there is a standard function for this:

=CELL("Filename")

which gives the filename and full path to it.


Cheers, Glenn.

 

[TonyJollans]

Hi Steph,

Glenn is correct. [purple]Cell("Filename")[/purple] gives the full path, right down to the sheet name, but only if the workbook has been saved. To extract the sheet name needs something like ..

[blue][tt]=MID(CELL("filename"),FIND("]",CELL("filename"))+1,32)[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

<cough>
faq68-2561
</cough>

;-)

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[TonyJollans]

Nasty cough you've got there, Geoff

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

I know - must move into the coutryside - this city air's a killer ;-)

Just updated the FAQ as well - some of the formulae were a bit messy

Rgds, Geoff

 

[Sareimer]

Thanks to everyone....
Dan's solution works just fine. I only wanted the worksheet name because I needed it for another formula reference (dynamic). Since I'm not so advanced on the VB I wanted a forumula without all the other junk (path etc).

I appreciate the option!! You guys rock!

By the way... Mountain air is best!

 

[xlbo]

If you'd actually read the FAQ, you would've noticed that this FORMULA:
=RIGHT(CELL("FILENAME",F10),LEN(CELL("FILENAME",F10))-FIND("]",CELL("FILENAME",F10)))

returns the sheet name only

Dan's solution DOES use VBA - and also, if you want to continue using it, you should put

Application.Volatile

as the 1st line of code as otherwise, it will not update when you change the sheet name

Rgds, Geoff