Worksheet tab name

D

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
 
M

Mike H

Hi,

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

Mike
 
D

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.

Mike said:
Hi,

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

Mike

DeanH said:
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
Click to expand...
Click to expand...
 
D

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


Mike H said:
Hi,

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

Mike

DeanH said:
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
Click to expand...
Click to expand...
 
M

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

Dave Peterson said:
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 said:
Hi,

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

Mike

DeanH said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

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
 
D

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
 
J

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 :)
 
D

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

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


Mike said:
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

Dave Peterson said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top