Worksheet name

Vernon Blake

unread,

Apr 21, 2003, 1:30:32 AM4/21/03

to

I would like a function that will return the name of the
current worksheet for a specified cell within that
worksheet.

Mark Rosenkrantz

unread,

Apr 21, 2003, 4:05:49 AM4/21/03

to

Vernon;

Try these;

Function Workbookname() As String
' Returns the workbook name in a cell.
Workbookname = Application.Caller.Parent.Parent.Name
End Function

Function Worksheetname() As String
' Returns the sheet name in a cell.
Worksheetname = Application.Caller.Parent.Name
End Function

Now select any cell in the worksheet and type:
Workbookname()
Worksheetname()

Succes;
Mark Rosenkrantz.

For more on Excel: www.rosenkrantz.nl or in...@rosenkrantz.nl

"Vernon Blake" <bla...@dot.state.al.us> wrote in message
news:05c001c307c7$20f17fd0$3101...@phx.gbl...

Arvi Laanemets

unread,

Apr 21, 2003, 4:16:20 AM4/21/03

to

Hi

The formula:
=CELL("Filename",INDIRECT("A1"))
returns someting like
Drive:\Path\[FileName.xls]SheetName

You easily can extract the worksheet's name from this string:
=MID(CELL("filename", A1),FIND("]", CELL("filename",A1))+1,32)
(It's extracted 32 characters, while in Excel worksheets name lenght is
limited with this number. Of-course you can use any number between 32 and
254).

You can use the formula above also in form:
=MID(CELL("filename", SheetnAme!A1),FIND("]",
CELL("filename",SheetName!A1))+1,32)
to get a sheet name for any sheet in workbook. It doesn't seem to have any
sin at glance, but you have to remember, that the reference changes, when
you rename the sheets.

Another way is to write an UDF:

Public Function NameOfSheet(N As Integer) As Variant
Application.Volatile
NameOfSheet= Application.Sheets(N).Name
End Function

The parameter N is an index of sheet in Tab order. Entering into some cell
the formula:
=IF(ISERROR(NameOfSheet(ROW(A1))),"",NameOfSheet(ROW(A1)))
and copying it down long enough gives you the list of all worksheets,
present in your workbook. The advantage, compared to formula above, is that
you can have the formula ready before you have actually the sheet created.
Or you use the UDF to simply get the name of N'th sheet in your workbook -
but it changes when you change sheets order.

Arvi Laanemets

"Vernon Blake" <bla...@dot.state.al.us> wrote in message
news:05c001c307c7$20f17fd0$3101...@phx.gbl...

Ken Wright

unread,

Apr 21, 2003, 4:13:32 AM4/21/03

to

From an old post of Peo's

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34)

workbook has to be saved..

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Polite Request - It is very very much appreciated in
text-only groups if you do not attach files - Thanks
----------------------------------------------------------------------------

"Vernon Blake" <bla...@dot.state.al.us> wrote in message news:05c001c307c7$20f17fd0$3101...@phx.gbl...

Harlan Grove

unread,

Apr 21, 2003, 5:00:09 PM4/21/03

to

"Vernon Blake" wrote...

>I would like a function that will return the name of the
>current worksheet for a specified cell within that
>worksheet.

You've gotten the standard reply 3 times now. If you'd need this on each
worksheet (or most of them) and would prefer something short, you could define a
name like _WSN referring to the formula

=MID(CELL("Filename",INDIRECT("A1")),
FIND("]",CELL("Filename",INDIRECT("A1")))+1,
32)

Then you could use _WSN in formulas on each worksheet, and it'll return the
expected worksheet name. This won't save recalc time, but it will make formulas
shorter.

--
Public Service Announcements:
1. Don't attach files to postings in this newsgroup.
2. Learn how to snip unnecessary text from quoted material. Indiscriminate
quoting wastes more bandwidth than file attachments.