# Display Sheet Name in a Cell

#### snolze123

##### New Member
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.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### VoG

##### Legend
Try

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

#### Richard Schollar

##### MrExcel MVP
I'm rather partial to:

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

#### VoG

##### Legend
I'm rather partial to:

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

That's certainly neater than mine

Perfect. Thanks!

#### mgirvin

##### Well-known Member
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

##### Legend
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

##### Well-known Member
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

##### Legend
BTW, you can also use code. In the ThisWorkBook Module
Code:
``````Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("\$A\$1") = "Territory " & ActiveSheet.Name
End Sub``````

lenze

Dear lenze,

EXCELlent!!!

Thanks.

Replies
31
Views
757
Replies
1
Views
171
Replies
3
Views
126
Replies
2
Views
54
Replies
0
Views
382

1,191,483
Messages
5,986,851
Members
440,053
Latest member
jhollingworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back