If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Returning Worksheet Name
Another simple question...
How do you reference the name of the current worksheet? For example: if the current worksheet is called "Week1", I would like to set A1=1 ... if the current worksheet is called "Week52", I would like to set A1=52 --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Returning Worksheet Name
Here are a few file alternatives to a cell, including sheet name
File path and file name: =CELL("filename",A1) File path only =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) File name only =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fi lename",A1),1)-FIND("[",CELL("filename",A1),1)-1) The sheet name =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename" ,A1),1)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "sandbag " wrote in message ... Another simple question... How do you reference the name of the current worksheet? For example: if the current worksheet is called "Week1", I would like to set A1=1 .. if the current worksheet is called "Week52", I would like to set A1=52 --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Returning Worksheet Name
Hi Sandbag!
One way: =RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32),LEN(MID(CELL("filenam e",A1),FIND("]",CELL("filename",A1))+1,32))-4) It's based upon a parsing of the formula for finding the sheet name: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) The workbook must have been saved for the formulas to work. -- Regards Norman Harker MVP (Excel) Sydney, Australia "sandbag " wrote in message ... Another simple question... How do you reference the name of the current worksheet? For example: if the current worksheet is called "Week1", I would like to set A1=1 .. if the current worksheet is called "Week52", I would like to set A1=52 --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Returning Worksheet Name
Again many thanks to all who replied.
CELL is the answer. Happy Sandbag --- Message posted from http://www.ExcelForum.com/ |
#5
|
|||
|
|||
Returning Worksheet Name
"Bob Phillips" wrote...
.... File path and file name: =CELL("filename",A1) Quibble: this is path, file *AND* worksheet name. The sheet name =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1 ))-FIND("]", CELL("filename",A1),1)) .... There's no need to waste cycles on LEN or a 3rd CELL call. =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,32) |
#6
|
|||
|
|||
Returning Worksheet Name
"Harlan Grove" wrote in message ... "Bob Phillips" wrote... ... File path and file name: =CELL("filename",A1) Quibble: this is path, file *AND* worksheet name. Indeed it is. I will update my reference. The sheet name =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1 ))-FIND("]", CELL("filename",A1),1)) ... There's no need to waste cycles on LEN or a 3rd CELL call. =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,32) I think we have been here before. I'll change just to avoid a third timeG |
Thread Tools | |
Display Modes | |
|
|