Chip Pearson said: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 said: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.
HR Duvall said: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 said: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
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.
Chip Pearson said: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 said: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 said: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
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.
Thats fine but it always inserts the name of the acitve worksheet into all
sheets so they have the same name
Chip Pearson said: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 said: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
:
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
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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.