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




"Marcus Fox" <[email protected]> wrote in
message news:[email protected]...
>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
>
>

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Marcus Fox" <[email protected]> wrote in
message news:[email protected]...
> 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
>
>

[Marcus Fox]

"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:%[email protected]...
> 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]

"Marcus Fox" <[email protected]> wrote in
message news:[email protected]...
>
> "Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
> news:%[email protected]...
> > 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]

"Marcus Fox" <[email protected]> wrote in
message news:[email protected]...
>
> "Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
> news:%[email protected]...
>> 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/

"Marcus Fox" <[email protected]> wrote in
message news:[email protected]...
>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
>
>