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
|
|||
|
|||
inserting worksheet name in cell using function
I have found a formula on the web help site to
automatically insert a worksheet name into a cell of the worksheet. I have several worksheets and wish to identify each worksheet automatically in a cell of each worksheet. When I use the formula it initially brings in the correct worksheet name. However, when I select another worksheet the prior name appears in the selected cell. Perhaps I need to change something in my formula or this is a problem that I can't correct. What is your suggestion? The following formula is what I am using in each worksheet. =MID(CELL("filename"),SEARCH("[",CELL("filename")) +18,SEARCH("]",CELL("filename"))-2 ) Perhaps there is a simpler way to insert the worksheet name into a cell. Something like; =cell("worksheet"), or =cell("filename,-path"). I haven't been able to find a solution. Please advise. |
#2
|
|||
|
|||
inserting worksheet name in cell using function
1. You have to save the workbook first
2. You need to put in a cell reference or else it will return the active sheet's name =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) will do it -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom wrote in message ... I have found a formula on the web help site to automatically insert a worksheet name into a cell of the worksheet. I have several worksheets and wish to identify each worksheet automatically in a cell of each worksheet. When I use the formula it initially brings in the correct worksheet name. However, when I select another worksheet the prior name appears in the selected cell. Perhaps I need to change something in my formula or this is a problem that I can't correct. What is your suggestion? The following formula is what I am using in each worksheet. =MID(CELL("filename"),SEARCH("[",CELL("filename")) +18,SEARCH("]",CELL("filename"))-2 ) Perhaps there is a simpler way to insert the worksheet name into a cell. Something like; =cell("worksheet"), or =cell("filename,-path"). I haven't been able to find a solution. Please advise. |
#3
|
|||
|
|||
inserting worksheet name in cell using function
Alternate method.......
User Defined Function(which is slower than the builtin that Peo provides), but easier to enter and does not require the workbook to be saved first. Function ShtName(Optional ByVal rng As Range) As String Application.Volatile If rng Is Nothing Then Set rng = Application.Caller ShtName = rng.Parent.Name End Function Gord Dibben Excel MVP On Mon, 24 May 2004 20:26:34 -0400, "Peo Sjoblom" wrote: 1. You have to save the workbook first 2. You need to put in a cell reference or else it will return the active sheet's name =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) will do it |
Thread Tools | |
Display Modes | |
|
|