Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
tab name in the body of a worksheet - &[TAB]?
I am trying to find an easy way to insert the tab name in the body of the
worksheet. I know in headers & footers you can use &[TAB] but I can't seem to make that work in the worksheet. Any help will be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
tab name in the body of a worksheet - &[TAB]?
Try the following formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) The workbook must have been saved to disk for this to work. Note that you need the word "filename" in the formula, as it is written, NOT the actual filename. You can replace the A1 with any cell you like. It doesn't matter which cell you use, just so long as it is on the same worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "HR Duvall" wrote in message ... I am trying to find an easy way to insert the tab name in the body of the worksheet. I know in headers & footers you can use &[TAB] but I can't seem to make that work in the worksheet. Any help will be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
tab name in the body of a worksheet - &[TAB]?
WOW!! I'm not sure how you did it but it works perfectly. I am trying to
understand how the formula but it's beyond me. Thanks you very much!! Helen "Chip Pearson" wrote: Try the following formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) The workbook must have been saved to disk for this to work. Note that you need the word "filename" in the formula, as it is written, NOT the actual filename. You can replace the A1 with any cell you like. It doesn't matter which cell you use, just so long as it is on the same worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "HR Duvall" wrote in message ... I am trying to find an easy way to insert the tab name in the body of the worksheet. I know in headers & footers you can use &[TAB] but I can't seem to make that work in the worksheet. Any help will be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
tab name in the body of a worksheet - &[TAB]?
It is actually a fairly simple formula.
CELL("filename",A1) returns a string with the full filename and the worksheet name. For example "H:\[Book1.xls]Sheet1 The FIND function looks in this string for the ']' character and returns its position in the string. The next character after the ']' is the beginning of the sheet name, so we add 1 to the result of FIND. The MID function returns a portion of a string. You pass it the string itself, the starting point within the string, and the number of characters to return. In this case, we pass the string returned by CELL("filename",A1), to MID, and the result of FIND +1 as the starting point. For the number of characters, we pass 255. This can be any number longer than the maximum length of a sheet name, which is currently 31. The purpose of the A1 in the CELL command is to ensure that CELL references the same sheet as the formula. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "HR Duvall" wrote in message ... WOW!! I'm not sure how you did it but it works perfectly. I am trying to understand how the formula but it's beyond me. Thanks you very much!! Helen "Chip Pearson" wrote: Try the following formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) The workbook must have been saved to disk for this to work. Note that you need the word "filename" in the formula, as it is written, NOT the actual filename. You can replace the A1 with any cell you like. It doesn't matter which cell you use, just so long as it is on the same worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "HR Duvall" wrote in message ... I am trying to find an easy way to insert the tab name in the body of the worksheet. I know in headers & footers you can use &[TAB] but I can't seem to make that work in the worksheet. Any help will be greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
tab name in the body of a worksheet - &[TAB]?
Thats fine but it always inserts the name of the acitve worksheet into all
sheets so they have the same name "Chip Pearson" wrote: It is actually a fairly simple formula. CELL("filename",A1) returns a string with the full filename and the worksheet name. For example "H:\[Book1.xls]Sheet1 The FIND function looks in this string for the ']' character and returns its position in the string. The next character after the ']' is the beginning of the sheet name, so we add 1 to the result of FIND. The MID function returns a portion of a string. You pass it the string itself, the starting point within the string, and the number of characters to return. In this case, we pass the string returned by CELL("filename",A1), to MID, and the result of FIND +1 as the starting point. For the number of characters, we pass 255. This can be any number longer than the maximum length of a sheet name, which is currently 31. The purpose of the A1 in the CELL command is to ensure that CELL references the same sheet as the formula. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "HR Duvall" wrote in message ... WOW!! I'm not sure how you did it but it works perfectly. I am trying to understand how the formula but it's beyond me. Thanks you very much!! Helen "Chip Pearson" wrote: Try the following formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) The workbook must have been saved to disk for this to work. Note that you need the word "filename" in the formula, as it is written, NOT the actual filename. You can replace the A1 with any cell you like. It doesn't matter which cell you use, just so long as it is on the same worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "HR Duvall" wrote in message ... I am trying to find an easy way to insert the tab name in the body of the worksheet. I know in headers & footers you can use &[TAB] but I can't seem to make that work in the worksheet. Any help will be greatly appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
tab name in the body of a worksheet - &[TAB]?
You must have left out the A1 reference CELL("filename",A1)
Gord Dibben MS Excel MVP On Thu, 14 Feb 2008 16:44:01 -0800, Sandles wrote: Thats fine but it always inserts the name of the acitve worksheet into all sheets so they have the same name "Chip Pearson" wrote: It is actually a fairly simple formula. CELL("filename",A1) returns a string with the full filename and the worksheet name. For example "H:\[Book1.xls]Sheet1 The FIND function looks in this string for the ']' character and returns its position in the string. The next character after the ']' is the beginning of the sheet name, so we add 1 to the result of FIND. The MID function returns a portion of a string. You pass it the string itself, the starting point within the string, and the number of characters to return. In this case, we pass the string returned by CELL("filename",A1), to MID, and the result of FIND +1 as the starting point. For the number of characters, we pass 255. This can be any number longer than the maximum length of a sheet name, which is currently 31. The purpose of the A1 in the CELL command is to ensure that CELL references the same sheet as the formula. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "HR Duvall" wrote in message ... WOW!! I'm not sure how you did it but it works perfectly. I am trying to understand how the formula but it's beyond me. Thanks you very much!! Helen "Chip Pearson" wrote: Try the following formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) The workbook must have been saved to disk for this to work. Note that you need the word "filename" in the formula, as it is written, NOT the actual filename. You can replace the A1 with any cell you like. It doesn't matter which cell you use, just so long as it is on the same worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "HR Duvall" wrote in message ... I am trying to find an easy way to insert the tab name in the body of the worksheet. I know in headers & footers you can use &[TAB] but I can't seem to make that work in the worksheet. Any help will be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet not showing up in VBE | Excel Worksheet Functions | |||
Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |