Text same as worksheet name

[Marcus Fox]

I have some worksheets named on the worksheet tabs. Is there a formula I can
add to a line of text that will return the worksheet name? For example
"Monthly Summary Sheet for [worksheet] 2006"

Marcus

 

[Peo Sjoblom]

One way

="Monthly Summary Sheet for
"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)&" "&"2006"

note that the workbook has to be saved before you can use the formula

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.xlFAQ0002.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Marcus Fox]

One way

="Monthly Summary Sheet for
"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)&" "&"2006"

note that the workbook has to be saved before you can use the formula

Why? I am looking for the formula to return the name of the worksheet, not
the workbook. But thanks, that formula is handy for another idea I have
later.

Marcus

 

[Marcus Fox]

One way

="Monthly Summary Sheet for
"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)&" "&"2006"

note that the workbook has to be saved before you can use the formula

Why? I am looking for the formula to return the name of the worksheet, not
the workbook. But thanks, that formula is handy for another idea I have
later.

OK, looks like that works LOL.

Thanks again.

Marcus

 

[Peo Sjoblom]

One way

="Monthly Summary Sheet for
"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)&" "&"2006"

note that the workbook has to be saved before you can use the formula

Why? I am looking for the formula to return the name of the worksheet, not
the workbook. But thanks, that formula is handy for another idea I have
later.

Because otherwise the cell("filename" will return an error because it will
give you the path and name of the workbook plus the sheet (mid just extract
the sheet name from that whole path, just test with CELL("filename",A1)) and
since it isn't saved it is in tmp file limbo

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Martin Rice]

Marcus

You could try a user defined function like

Function WorksheetName(Cell)
WorksheetName = "Monthly Summary Sheet for " & Cell.Parent.Name & " 2006"
End Function





Martin

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Visit Eighty-Twenty Spreadsheet Automation for professional customised
spreadsheet development

http://homepage.ntlworld.com/martin.rice1/