| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet tab name
use CELL function
|
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unable to Insert Worksheet/Move and copy worksheet Excel 2003 | Excel Worksheet Functions | |||
| plot graph from multiple worksheet as embedded chart object on every worksheet | Charts and Charting in Excel | |||
| plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Worksheet Functions | |||
| plot graph from multiple worksheet as embedded chart object on every worksheet | New Users to Excel | |||
| Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
Linear Mode
