Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how to get the name of the sheet in a cell?
Hello, I would like to know if there's a function or a macro to put the name of the current sheet in a cell like ="this sheet is called"&<function that returns the name of the sheet so that if a sheet's name is "kaput" it should appear "this sheet is called kaput" Tks Alexandra -- Alexandra ------------------------------------------------------------------------ Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707 View this thread: http://www.excelforum.com/showthread...hreadid=399701 |
#2
|
|||
|
|||
Good morning Alexandra AFAIK there isn't a native Excel function that will do this, but you could write one very simply. This would do the trick: Function SheetName() SheetName = ActiveSheet.Name End Function Then the formula =Sheetname() would return the sheetname, whilst this in a cell: ="This sheet is called " & Sheetname() would return exactly what you requested. HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=399701 |
#3
|
|||
|
|||
Hi,
In cell C4 in the sheet1, type the following formula =cell("filename") Now save and close the worksheet and reopen it. In the cell in which you want the particular worksheet name type the following array formula (Ctrl+Shift+Enter), "This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15) Regards, Ashish Mathur "Alexandra" wrote: Hello, I would like to know if there's a function or a macro to put the name of the current sheet in a cell like ="this sheet is called"&<function that returns the name of the sheet so that if a sheet's name is "kaput" it should appear "this sheet is called kaput" Tks Alexandra -- Alexandra ------------------------------------------------------------------------ Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707 View this thread: http://www.excelforum.com/showthread...hreadid=399701 |
#4
|
|||
|
|||
See
http://www.mcgimpsey.com/excel/formu..._function.html ..In article , Alexandra wrote: Hello, I would like to know if there's a function or a macro to put the name of the current sheet in a cell like ="this sheet is called"&<function that returns the name of the sheet so that if a sheet's name is "kaput" it should appear "this sheet is called kaput" Tks Alexandra |
#5
|
|||
|
|||
Alexandra,
Excel can do what you want. Put in any cell: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30) Jack Sons The Netherlands "Alexandra" schreef in bericht ... Hello, I would like to know if there's a function or a macro to put the name of the current sheet in a cell like ="this sheet is called"&<function that returns the name of the sheet so that if a sheet's name is "kaput" it should appear "this sheet is called kaput" Tks Alexandra -- Alexandra ------------------------------------------------------------------------ Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707 View this thread: http://www.excelforum.com/showthread...hreadid=399701 |
#6
|
|||
|
|||
http://www.bygsoftware.com/Excel/functions/cell.htm
Workbook info using functions (and no VBA). Copy the formulas into your workbook (If the workbook is new and has not been saved these formulas will not work - there's no information for them to return!) The file path and name =CELL("filename",A1) The file path =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) The file name =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)) -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Alexandra" wrote in message ... Hello, I would like to know if there's a function or a macro to put the name of the current sheet in a cell like ="this sheet is called"&<function that returns the name of the sheet so that if a sheet's name is "kaput" it should appear "this sheet is called kaput" Tks Alexandra -- Alexandra ------------------------------------------------------------------------ Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707 View this thread: http://www.excelforum.com/showthread...hreadid=399701 |
#7
|
|||
|
|||
Note that you should use something like
=CELL("filename",A1) instead. If you don't put in the cell reference, CELL() returns the name of the last worksheet calculated, which may not be the sheet the cell resides in. There's no need to close the workbook and reopen it, though it does need to be saved. In article , "Ashish Mathur" wrote: In cell C4 in the sheet1, type the following formula =cell("filename") Now save and close the worksheet and reopen it. |
#8
|
|||
|
|||
Couple of quibbles:
1) You should use the reference argument to CELL() (e.g., CELL("fileneame", A1)) or it will return the name of the sheet in which the last cell was changed/calculated. This may be a different sheet than the one that the formula resides in. 2) Worksheet names can be 31 characters long. Using 30 in your MID formula could truncate the worksheet name. I use 255 (2^8 - 1) just to indicate a big number). In article , "Jack Sons" wrote: Excel can do what you want. Put in any cell: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30) |
#9
|
|||
|
|||
Thanks JE, I didn't think of it.
Jack. "JE McGimpsey" schreef in bericht ... Couple of quibbles: 1) You should use the reference argument to CELL() (e.g., CELL("fileneame", A1)) or it will return the name of the sheet in which the last cell was changed/calculated. This may be a different sheet than the one that the formula resides in. 2) Worksheet names can be 31 characters long. Using 30 in your MID formula could truncate the worksheet name. I use 255 (2^8 - 1) just to indicate a big number). In article , "Jack Sons" wrote: Excel can do what you want. Put in any cell: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell linked to a range of cell values in different sheet | Excel Discussion (Misc queries) | |||
Chart Title or Text Box Linked to cell & sheet | Charts and Charting in Excel | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Same cell added to master summary sheet | Excel Worksheet Functions | |||
referencing a sheet named in a cell then using data from that sheet | Excel Worksheet Functions |