Worksheet tab name
 

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

Worksheet tab name
 

use CELL function

Worksheet tab name
 

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)

"DeanH" wrote in message
...
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

Worksheet tab name
 

Hi,

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

Mike

"DeanH" wrote:

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

Worksheet tab name
 

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.

Mike H wrote:

Hi,

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

Mike

"DeanH" wrote:

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

--

Dave Peterson

Worksheet tab name
 

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


"Mike H" wrote:

Hi,

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

Mike

"DeanH" wrote:

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

Worksheet tab name
 

Spot on, the extra A1 reference helps.

"Bob Phillips" wrote:

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)

"DeanH" wrote in message
...
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

Worksheet tab name
 

Yes I know that. The OP asked

What I am after is when I rename a worksheet at the tab, I wont this
reflected in a cell on that worksheet.

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

"Dave Peterson" wrote:

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.

Mike H wrote:

Hi,

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

Mike

"DeanH" wrote:

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

--

Dave Peterson

Worksheet tab name
 

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



--
John C


"DeanH" wrote:

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

Worksheet tab name
 

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" wrote:

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



--
John C


"DeanH" wrote:

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

Worksheet tab name
 

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 :)
--
John C


"DeanH" wrote:

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" wrote:

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



--
John C


"DeanH" wrote:

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

Worksheet tab name
 

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.

"John C" wrote:

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 :)
--
John C


"DeanH" wrote:

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" wrote:

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



--
John C


"DeanH" wrote:

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

Worksheet tab name
 

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.)


Mike H wrote:

Yes I know that. The OP asked

What I am after is when I rename a worksheet at the tab, I wont this
reflected in a cell on that worksheet.

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

"Dave Peterson" wrote:

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.

Mike H wrote:

Hi,

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

Mike

"DeanH" wrote:

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

--

Dave Peterson


--

Dave Peterson