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
|
|||
|
|||
&[tab] Usage Twist
I would like to know if there is a way to get the "&[tab]"
name to appear in a cell on the worksheet itself, instead of as a header or footer. Any ideas? |
#2
|
|||
|
|||
&[tab] Usage Twist
Jody,
I copied a solution I saw earlier in an Excel forum. It was by Bob Phillips. When I see stuff I like, I categorize and save it. It addresses your question and more. I think you want formula #3. I thought you might like his response as reference. Best regards, Kevin ================================================== ========= Title: Sheet Names and Names in General Created On: 2 Jan 2004 By: Bob Phillips Keywords: sheet, name, path, cell, filename Notes: Posted to Newsgroup: Excel.worksheet.functions ================================================== ========== Creating Formulae The answer to this question lies in the 'CELL' worksheet formula. The following formula gives the full path and name of the current worksheet =CELL("filename") This returns a value that will look something like D:\Bob\My Documents\My Spreadsheets\[Premiership 2003.xls]League Table To retrieve just the sheet name, or even the workbook name of full path, we need to parse the result and extract the required value. 1. The workbook path is simply =LEFT(CELL("filename"),FIND("[",CELL("filename"))-2) In my example, this returns D:\Bob\My Documents\My Spreadsheets 2. The Workbook name is =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename") )-FIND("[",CELL("filename"))-1) Which returns Premiership 2003.xls 3. The sheet name is =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) This final formula returns League Table So there we have it, 3 formulae to get the workbook path, the workbook name and the worksheet name into a worksheet cell. Restrictions This technique only works for workbooks that have been saved, at least once. One Big Problem There is one major problem with formula 3, the worksheet name. Try this to highlight the problem. a.. Enter the formula in a cell on Sheet1, say A1. As expected, you will see the value 'Sheet1' in the cell b.. Then enter the same formula in A1 on Sheet2. Again, as expected, you will see the value 'Sheet2' in A1 c.. Go back to Sheet1, A1 now says Sheet2 The problem here is that every time worksheet recalculation takes place, each instance of the formula resolves itself to the active worksheet, not the worksheet that the instance is necessarily on. Fortunately, this is simply resolved by adding a reference to a cell, any cell, to the formula, and this anchors the formula to the worksheet it is on. So, the worksheet name formula then becomes =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) It matters not which cell is used, it is only a cell reference that is needed to anchor the formula. Making a Generic Routine Since using the worksheet name technique, it occurred to me that it would be useful to have it available to any workbook that I opened. This seemed easy enough to do, just create a named range with the above formula in the new workbook template (Book.let in the XLStart directory), using the following steps: a.. goto menu InsertNameDefine ... b.. add this value to the 'Names In Workbook' input box sh.name c.. add this formula to the 'Refers To:' input box =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) Thus, every time a workbook was 'NEWed', the workbook would have a workbook name that could be used to get the worksheet name (after it has been saved of course), simply by putting the formula =sh.name in a cell. Unfortunately, this doesn't quite work as it should. As before, the value on each worksheet is always the same, even though we added the cell reference. Unlike before, the value does not get reset every recalculation, but is assigned the name of the worksheet that was active when the name was defined, and only that name. This is due to the fact that when a workbook name is defined, Excel prefixes any range reference with the name of active worksheet. For instance, if 'Sheet1' was active when the name sh.name was defined, the formula would finish up as =MID(CELL("filename",Sheet1!A1),FIND("]",CELL("filename",Sheet1!A1))+1,255) thereby negating the effect of adding the cell reference. What we need to do is create a formula that prefixes the worksheet, but no particular worksheet, so that we have a variable workbook name that enables the formula to work correctly in each worksheet, but prevents Excel from adding the active worksheet name as a prefix. Fortunately, this can be achieved by simply adding a worksheet delimiter, namely the '!'. Thus the formula becomes =MID(CELL("filename",!A1),FIND("]",CELL("filename",!A1))+1,255) Now, =sh.name returns Sheet1 on Sheet1, and Sheet2 on Sheet2. Thus, we now have a fully flexible, domprehensive routine to return the worksheet name of any worksheet, in any newly created workbook. And Finally ... In my base workbook template, I have defined a number of workbook names that incrementally build on the basic CELL formula. I list these below, and leave you to work them out, and use or not use as you see fit. file.full =CELL("filename",!$A$1) file.fullname =LEFT(file.full,FIND("]",file.full)) file.name =MID(file.fullname,FIND("[",file.fullname)+1,LEN(file.fullname)-FIND("[",fil e.fullname)-1) file.dir =LEFT(file.full,FIND("[",file.full)-1) file.sheet.name =MID(file.full,FIND("]",file.full)+1,255) -- HTH "Jody" wrote in message ... I would like to know if there is a way to get the "&[tab]" name to appear in a cell on the worksheet itself, instead of as a header or footer. Any ideas? |
#3
|
|||
|
|||
&[tab] Usage Twist
Jody
Enter the following formula anywhere on the worksheet besides cell A1. If you need to enter it into cell A1, change the reference to some other cell. Which cell it references doesn't matter. File must be saved once before it will work. =MID(CELL("FileName",A1),FIND("]",CELL("FileName",A1))+1,999) Gord Dibben Excel MVP On Thu, 8 Jan 2004 15:36:24 -0800, "Jody" wrote: I would like to know if there is a way to get the "&[tab]" name to appear in a cell on the worksheet itself, instead of as a header or footer. Any ideas? |
#4
|
|||
|
|||
&[tab] Usage Twist
Kevin,
Thank you very much! You can't imagine how much this is going to help me. I appreciate you taking the time to reply. Jody -----Original Message----- Jody, I copied a solution I saw earlier in an Excel forum. It was by Bob Phillips. When I see stuff I like, I categorize and save it. It addresses your question and more. I think you want formula #3. I thought you might like his response as reference. Best regards, Kevin ================================================= ========= = Title: Sheet Names and Names in General Created On: 2 Jan 2004 By: Bob Phillips Keywords: sheet, name, path, cell, filename Notes: Posted to Newsgroup: Excel.worksheet.functions ================================================= ========= == Creating Formulae The answer to this question lies in the 'CELL' worksheet formula. The following formula gives the full path and name of the current worksheet =CELL("filename") This returns a value that will look something like D:\Bob\My Documents\My Spreadsheets\[Premiership 2003.xls] League Table To retrieve just the sheet name, or even the workbook name of full path, we need to parse the result and extract the required value. 1. The workbook path is simply =LEFT(CELL("filename"),FIND("[",CELL("filename"))-2) In my example, this returns D:\Bob\My Documents\My Spreadsheets 2. The Workbook name is =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND ("]",CELL("filename") )-FIND("[",CELL("filename"))-1) Which returns Premiership 2003.xls 3. The sheet name is =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) This final formula returns League Table So there we have it, 3 formulae to get the workbook path, the workbook name and the worksheet name into a worksheet cell. Restrictions This technique only works for workbooks that have been saved, at least once. One Big Problem There is one major problem with formula 3, the worksheet name. Try this to highlight the problem. a.. Enter the formula in a cell on Sheet1, say A1. As expected, you will see the value 'Sheet1' in the cell b.. Then enter the same formula in A1 on Sheet2. Again, as expected, you will see the value 'Sheet2' in A1 c.. Go back to Sheet1, A1 now says Sheet2 The problem here is that every time worksheet recalculation takes place, each instance of the formula resolves itself to the active worksheet, not the worksheet that the instance is necessarily on. Fortunately, this is simply resolved by adding a reference to a cell, any cell, to the formula, and this anchors the formula to the worksheet it is on. So, the worksheet name formula then becomes =MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) +1,255) It matters not which cell is used, it is only a cell reference that is needed to anchor the formula. Making a Generic Routine Since using the worksheet name technique, it occurred to me that it would be useful to have it available to any workbook that I opened. This seemed easy enough to do, just create a named range with the above formula in the new workbook template (Book.let in the XLStart directory), using the following steps: a.. goto menu InsertNameDefine ... b.. add this value to the 'Names In Workbook' input box sh.name c.. add this formula to the 'Refers To:' input box =MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) +1,255) Thus, every time a workbook was 'NEWed', the workbook would have a workbook name that could be used to get the worksheet name (after it has been saved of course), simply by putting the formula =sh.name in a cell. Unfortunately, this doesn't quite work as it should. As before, the value on each worksheet is always the same, even though we added the cell reference. Unlike before, the value does not get reset every recalculation, but is assigned the name of the worksheet that was active when the name was defined, and only that name. This is due to the fact that when a workbook name is defined, Excel prefixes any range reference with the name of active worksheet. For instance, if 'Sheet1' was active when the name sh.name was defined, the formula would finish up as =MID(CELL("filename",Sheet1!A1),FIND("]",CELL ("filename",Sheet1!A1))+1,255) thereby negating the effect of adding the cell reference. What we need to do is create a formula that prefixes the worksheet, but no particular worksheet, so that we have a variable workbook name that enables the formula to work correctly in each worksheet, but prevents Excel from adding the active worksheet name as a prefix. Fortunately, this can be achieved by simply adding a worksheet delimiter, namely the '!'. Thus the formula becomes =MID(CELL("filename",!A1),FIND("]",CELL("filename",!A1)) +1,255) Now, =sh.name returns Sheet1 on Sheet1, and Sheet2 on Sheet2. Thus, we now have a fully flexible, domprehensive routine to return the worksheet name of any worksheet, in any newly created workbook. And Finally ... In my base workbook template, I have defined a number of workbook names that incrementally build on the basic CELL formula. I list these below, and leave you to work them out, and use or not use as you see fit. file.full =CELL("filename",!$A$1) file.fullname =LEFT(file.full,FIND("]",file.full)) file.name =MID(file.fullname,FIND("[",file.fullname)+1,LEN (file.fullname)-FIND("[",fil e.fullname)-1) file.dir =LEFT(file.full,FIND("[",file.full)-1) file.sheet.name =MID(file.full,FIND("]",file.full) +1,255) -- HTH "Jody" wrote in message ... I would like to know if there is a way to get the "& [tab]" name to appear in a cell on the worksheet itself, instead of as a header or footer. Any ideas? . |
#5
|
|||
|
|||
&[tab] Usage Twist
Hi Jody,
My pleasure. And thank you for letting me know that the information was helpful. Best regards, Kevin "Jody" wrote in message ... Kevin, Thank you very much! You can't imagine how much this is going to help me. I appreciate you taking the time to reply. Jody |
Thread Tools | |
Display Modes | |
|
|