Worksheet tab name

[DeanH]

Excel 2003 on XP.
What is the syntax to return the worksheet tab name in a cell?
What I am after is when I rename a worksheet at the tab, I wont this
reflected in a cell on that worksheet.
Or is it possible that when I copy a previous worksheet, a cell
automatically creates a new sequential number, ie if the latest number is 15
(that is a tab is named 15 an a cell reflects this number) if I copy this
worksheet (or any other in this file) the cell changes to 16, and I obviously
would want the Tab to be renamed 16 as well.

Make sense?
Many thanks
DeanH

 

[Jarek Kujawa]

use CELL function

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.xlFAQ0002.html

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Hi,

Sheet name
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
The workbook must be saved for this to work

Mike

 

[Dave Peterson]

This formula will return the name of the worksheet that is active when excel
recalculates--not the name of the worksheet that contains the cell with the
formula.

Hi,

Sheet name
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
The workbook must be saved for this to work

Mike

Excel 2003 on XP.
What is the syntax to return the worksheet tab name in a cell?
What I am after is when I rename a worksheet at the tab, I wont this
reflected in a cell on that worksheet.
Or is it possible that when I copy a previous worksheet, a cell
automatically creates a new sequential number, ie if the latest number is 15
(that is a tab is named 15 an a cell reflects this number) if I copy this
worksheet (or any other in this file) the cell changes to 16, and I obviously
would want the Tab to be renamed 16 as well.

Make sense?
Many thanks
DeanH

 

[DeanH]

Spot on, fantastic.
I also have noticed that this cell will update once you have done another
edit anywhere in the sheet or workbook, without a save being done.
Many thanks, have a great weekend
DeanH

Hi,

Sheet name
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
The workbook must be saved for this to work

Mike

Excel 2003 on XP.
What is the syntax to return the worksheet tab name in a cell?
What I am after is when I rename a worksheet at the tab, I wont this
reflected in a cell on that worksheet.
Or is it possible that when I copy a previous worksheet, a cell
automatically creates a new sequential number, ie if the latest number is 15
(that is a tab is named 15 an a cell reflects this number) if I copy this
worksheet (or any other in this file) the cell changes to 16, and I obviously
would want the Tab to be renamed 16 as well.

Make sense?
Many thanks
DeanH

 

[DeanH]

Spot on, the extra A1 reference helps.

See http://www.xldynamic.com/source/xld.xlFAQ0002.html

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Yes I know that. The OP asked

Given that renaming a sheet causes re-calculation and that the sheet will be
active when it is renamed I think it does what the OP asked.

Mike

This formula will return the name of the worksheet that is active when excel
recalculates--not the name of the worksheet that contains the cell with the
formula.

Hi,

Sheet name
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
The workbook must be saved for this to work

Mike

Excel 2003 on XP.
What is the syntax to return the worksheet tab name in a cell?
What I am after is when I rename a worksheet at the tab, I wont this
reflected in a cell on that worksheet.
Or is it possible that when I copy a previous worksheet, a cell
automatically creates a new sequential number, ie if the latest number is 15
(that is a tab is named 15 an a cell reflects this number) if I copy this
worksheet (or any other in this file) the cell changes to 16, and I obviously
would want the Tab to be renamed 16 as well.

Make sense?
Many thanks
DeanH

 

[John C]

Instead of that, you could just type the value into a cell, and use VBA
coding to change the tab name....
Assuming cell A1 is where you want your Tab name to appear.
Right click on tab, View Code, and insert the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then ActiveSheet.Name = Target

End Sub

 

[DeanH]

I will be passing this file onto another user and I don't want them to have
to use VBA, which I am sure they are most appreciatative of ;-)
The previous posting with the kb link works beautifully.
Thanks for the answer though. Have a great weekend.
DeanH

 

[John C]

Anytime. FYI, once you post the VBA, they don't have to do anything with it.
It will always activate when the cell with the tab reference is modified,
with no extra actions other than typing into the cell by the user. Don't even
have to right click on your tab

 

[DeanH]

That is very true but I have found that most of our PCs are set up with
secuirty high and they always winge about the Accept macro warning when
opening files.
Never mind.

 

[Dave Peterson]

It'll return the name of the sheet that's active when excel recalculates.

The activesheet isn't always the one with that formula.

Try this:

Start a new workbook
Make sure it has multiple worksheets (say 3)
window|new window (twice)
window|arrange|tiled
Save the workbook
Put your formula in A1 of each of the worksheets.

And recalculate.

I don't think that this is what the OP wants.

(You can also see the same effect when a different workbook is active.)

Yes I know that. The OP asked

Given that renaming a sheet causes re-calculation and that the sheet will be
active when it is renamed I think it does what the OP asked.

Mike

This formula will return the name of the worksheet that is active when excel
recalculates--not the name of the worksheet that contains the cell with the
formula.

Hi,

Sheet name
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
The workbook must be saved for this to work

Mike

:

Excel 2003 on XP.
What is the syntax to return the worksheet tab name in a cell?
What I am after is when I rename a worksheet at the tab, I wont this
reflected in a cell on that worksheet.
Or is it possible that when I copy a previous worksheet, a cell
automatically creates a new sequential number, ie if the latest number is 15
(that is a tab is named 15 an a cell reflects this number) if I copy this
worksheet (or any other in this file) the cell changes to 16, and I obviously
would want the Tab to be renamed 16 as well.

Make sense?
Many thanks
DeanH