| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display tab name in cell
How can I display the ActiveSheet.name in cell C5 for instance?
TIA -- Traa Dy Liooar Jock |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
Hi,
copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
Thanks Eduardo
-- Traa Dy Liooar Jock "Eduardo" wrote: Hi, copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
I think you can safely leave the optional cell reference argument off of the
function calls as the file name would be the same no matter what cell is referenced... =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) -- Rick (MVP - Excel) "Eduardo" wrote in message ... Hi, copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
Eduardo has given you a worksheet formula solution to your question;
however, since you posted your question in the "programming" newsgroup, I thought you might be looking for a UDF (User Defined Function). The UDF for this question is quite simple... Function TabName() TabName = ActiveSheet.Name End Function To install the UDF, press Alt+F11 to go into the VB editor and click Insert/Module from its menu bar, then copy/paste the above code into the code window that opened up there. To use the UDF, just place this formula... =TabName() into any cell that you want the TabName to appear in. -- Rick (MVP - Excel) "Jock" wrote in message ... How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
Your welcome,
"Jock" wrote: Thanks Eduardo -- Traa Dy Liooar Jock "Eduardo" wrote: Hi, copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
This isn't true.
To test: Create a workbook with a couple of worksheets (and save it at least once) then use this formula (without the range reference). =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) And show multiple windows (window|new window in xl2003 menus) And recalculate (hit F9) and watch what each formula evaluates to. Rick Rothstein wrote: I think you can safely leave the optional cell reference argument off of the function calls as the file name would be the same no matter what cell is referenced... =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) -- Rick (MVP - Excel) "Eduardo" wrote in message ... Hi, copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
Nothing odd happens when I do that... the tab name appears the same in all
the windows for that formula. What are you suggesting should have happened? My XL2003 is at Service Pack 2 if that might matter. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... This isn't true. To test: Create a workbook with a couple of worksheets (and save it at least once) then use this formula (without the range reference). =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) And show multiple windows (window|new window in xl2003 menus) And recalculate (hit F9) and watch what each formula evaluates to. Rick Rothstein wrote: I think you can safely leave the optional cell reference argument off of the function calls as the file name would be the same no matter what cell is referenced... =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) -- Rick (MVP - Excel) "Eduardo" wrote in message ... Hi, copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
Rick,
I also thought he might like the UDF solution, but didn't offer it because he seemed to like the formula route well enough. However, when I created the UDF, it didn't always update when I manually CHANGED the sheet name. Then I added Application.Volatile vbTrue, and that seemed to make it work. Is that your experience also? Thanks, Eric "Rick Rothstein" wrote: Eduardo has given you a worksheet formula solution to your question; however, since you posted your question in the "programming" newsgroup, I thought you might be looking for a UDF (User Defined Function). The UDF for this question is quite simple... Function TabName() TabName = ActiveSheet.Name End Function To install the UDF, press Alt+F11 to go into the VB editor and click Insert/Module from its menu bar, then copy/paste the above code into the code window that opened up there. To use the UDF, just place this formula... =TabName() into any cell that you want the TabName to appear in. -- Rick (MVP - Excel) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
Good catch! I completely forgot about the possibility the sheet name might
get changed. As you indicated, this is how the code should have be constructed... Function TabName() Application.Volatile vbTrue TabName = ActiveSheet.Name End Function Thanks for catching that omission for me. -- Rick (MVP - Excel) "egun" wrote in message ... Rick, I also thought he might like the UDF solution, but didn't offer it because he seemed to like the formula route well enough. However, when I created the UDF, it didn't always update when I manually CHANGED the sheet name. Then I added Application.Volatile vbTrue, and that seemed to make it work. Is that your experience also? Thanks, Eric "Rick Rothstein" wrote: Eduardo has given you a worksheet formula solution to your question; however, since you posted your question in the "programming" newsgroup, I thought you might be looking for a UDF (User Defined Function). The UDF for this question is quite simple... Function TabName() TabName = ActiveSheet.Name End Function To install the UDF, press Alt+F11 to go into the VB editor and click Insert/Module from its menu bar, then copy/paste the above code into the code window that opened up there. To use the UDF, just place this formula... =TabName() into any cell that you want the TabName to appear in. -- Rick (MVP - Excel) |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
Without the reference to the cell, the formula will use the activesheet (in the
activeworkbook). Are you sure that you had multiple different windows in view? Rick Rothstein wrote: Nothing odd happens when I do that... the tab name appears the same in all the windows for that formula. What are you suggesting should have happened? My XL2003 is at Service Pack 2 if that might matter. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... This isn't true. To test: Create a workbook with a couple of worksheets (and save it at least once) then use this formula (without the range reference). =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) And show multiple windows (window|new window in xl2003 menus) And recalculate (hit F9) and watch what each formula evaluates to. Rick Rothstein wrote: I think you can safely leave the optional cell reference argument off of the function calls as the file name would be the same no matter what cell is referenced... =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) -- Rick (MVP - Excel) "Eduardo" wrote in message ... Hi, copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
Okay, I see what you mean. I really do hate the CELL function as it seems to
be an odd one as functions go. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... Without the reference to the cell, the formula will use the activesheet (in the activeworkbook). Are you sure that you had multiple different windows in view? Rick Rothstein wrote: Nothing odd happens when I do that... the tab name appears the same in all the windows for that formula. What are you suggesting should have happened? My XL2003 is at Service Pack 2 if that might matter. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... This isn't true. To test: Create a workbook with a couple of worksheets (and save it at least once) then use this formula (without the range reference). =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) And show multiple windows (window|new window in xl2003 menus) And recalculate (hit F9) and watch what each formula evaluates to. Rick Rothstein wrote: I think you can safely leave the optional cell reference argument off of the function calls as the file name would be the same no matter what cell is referenced... =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) -- Rick (MVP - Excel) "Eduardo" wrote in message ... Hi, copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
There are legal (from Windows standpoint) that make the =cell() formulas even
uglier. Rick Rothstein wrote: Okay, I see what you mean. I really do hate the CELL function as it seems to be an odd one as functions go. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... Without the reference to the cell, the formula will use the activesheet (in the activeworkbook). Are you sure that you had multiple different windows in view? Rick Rothstein wrote: Nothing odd happens when I do that... the tab name appears the same in all the windows for that formula. What are you suggesting should have happened? My XL2003 is at Service Pack 2 if that might matter. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... This isn't true. To test: Create a workbook with a couple of worksheets (and save it at least once) then use this formula (without the range reference). =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) And show multiple windows (window|new window in xl2003 menus) And recalculate (hit F9) and watch what each formula evaluates to. Rick Rothstein wrote: I think you can safely leave the optional cell reference argument off of the function calls as the file name would be the same no matter what cell is referenced... =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) -- Rick (MVP - Excel) "Eduardo" wrote in message ... Hi, copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display tab name in cell
There are legal (from Windows standpoint) *file names* that make the =cell()
formulas even uglier. (I left out a couple of words.) Dave Peterson wrote: There are legal (from Windows standpoint) that make the =cell() formulas even uglier. Rick Rothstein wrote: Okay, I see what you mean. I really do hate the CELL function as it seems to be an odd one as functions go. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... Without the reference to the cell, the formula will use the activesheet (in the activeworkbook). Are you sure that you had multiple different windows in view? Rick Rothstein wrote: Nothing odd happens when I do that... the tab name appears the same in all the windows for that formula. What are you suggesting should have happened? My XL2003 is at Service Pack 2 if that might matter. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... This isn't true. To test: Create a workbook with a couple of worksheets (and save it at least once) then use this formula (without the range reference). =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) And show multiple windows (window|new window in xl2003 menus) And recalculate (hit F9) and watch what each formula evaluates to. Rick Rothstein wrote: I think you can safely leave the optional cell reference argument off of the function calls as the file name would be the same no matter what cell is referenced... =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) -- Rick (MVP - Excel) "Eduardo" wrote in message ... Hi, copy the formula as follow in the cell where you want to display the name =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) If this was helpful please say yes. Thank you "Jock" wrote: How can I display the ActiveSheet.name in cell C5 for instance? TIA -- Traa Dy Liooar Jock -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find last cell in range with data, display cell address | Excel Worksheet Functions | |||
| display text in one cell as a number in an adjacent cell in excel | New Users to Excel | |||
| Display contents of cell in another cell as part of text string? | New Users to Excel | |||
| Conditional display - IF cell is blank, display next used row! | Excel Programming | |||
| Shortcut key to display change the display from displaying cell values to cell formulae | Excel Programming | |||
Linear Mode
