Display Sheet Name in a Cell

[snolze123]

How can I display the sheet name in the sheet itself? My sheets are named as territory numbers and I want cell A1 of each sheet to be something like

="Territory "&....

I know that this can be accomplished in the header/footer section but I'd prefer to have it in a cell if possible.

 

[VoG]

Try

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[Richard Schollar]

I'm rather partial to:

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

 

[VoG]

I'm rather partial to:

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

That's certainly neater than mine

 

[snolze123]

Perfect. Thanks!

 

[mgirvin]

Dear RichardSchollar & VoG II,

What is the benefit of having the cell reference A1 in the two formulas? I got both formulas to work without a cell reference. Here are the formulas I got to work:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

And

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

Is there a situation where leaving the A1 out would cause a problem?

 

[lenze]

The purpose of the A1 in both formulas is to anchor it to that workbook. Not needed if you only have 1 workbook open at a time, but with 2 or more open, Excel can get confused. I believe it was Aladin that posted a good discussion of this many years ago

lenze

 

[mgirvin]

Dear lenze,

Thank you for that! I have used formulas such as these without the A1 for years and I have always run into exactly that trouble. And I did not know why. Now I can amend my formulas and include the A1! Thanks lenze and Aladin!

 

[lenze]

BTW, you can also use code. In the ThisWorkBook Module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("$A$1") = "Territory " & ActiveSheet.Name
End Sub

lenze

 

[mgirvin]

Dear lenze,

EXCELlent!!!

Thanks.