What function returns the sheet name?

[baobob]

Oh sure, I know I can always parse CELL("filename"), which returns a
Webster's Collegiate Edition-length string whose last segment is the
sheet name. But, I mean, aghhh!

Doofus-user full disclosure dept.: I'm, uh, <gulp>, heh heh, running
Excel 97 SR2.

Thanks.

***

 

[Harlan Grove]

Oh sure, I know I can always parse CELL("filename"), which returns a
Webster's Collegiate Edition-length string whose last segment is the
sheet name. But, I mean, aghhh!

....

That's the only approach possible using only built-in functionality. The
only practical alternative is a user-defined function written in VBA.

Function wsn(Optional r As Range) As String
If r Is Nothing Then wsn = Application.Caller.Parent.Name _
Else wsn = r.Parent.Name
End Function

Use it in formulas like

=wsn()

to get the name of the worksheet containing the cell containing this
formula. Use it in formulas like

=wsn(SheetX!Y99)

to get the name of the worksheet containing the range passed as the argument
to wsn.

 

[baobob]

Harlan:

Thanks so much for your answer. I'll write a UDF pronto.

***

 

[baobob]

Harlan:

P.S. God damn! Works like a charm.

Not only am I still using Excel 97 SR2, but I've been a PC user since
1983. And altho' I've played around w/ VBA a bit before, thanks to
you, this is my first UDF.

Thanks again.

***

 

[Max Lamer]

You do not need a UDF. You can simply slice off the sheet name with text
functions from the cell("filename") function. I'm a bit lazy tonite, but you
can start with mid and find, looking for the "[" marking the begining of the
sheet name.


Max

 

[Harlan Grove]

You do not need a UDF. You can simply slice off the sheet name with text
functions from the cell("filename") function. I'm a bit lazy tonite, but
you can start with mid and find, looking for the "[" marking the begining
of the sheet name.

Reread the previous exchange, which I'll retain below for your convenience.
OP was already aware of using CELL("Filename"), but was asking for
alternatives. I responded by saying that was the only way to do it with
built-in functionality, by which I meant out-of-the-box worksheet functions.
The udf I gave was an ALTERNATIVE to parsing CELL("Filename").
....