Tab Names

P

Purshina

Has anyone figured out a way to reference the name of a
sheet in a cell?

I know it is possible to reference the name of a tab in
the footer/header....but I'd like to reference the tab
name in the worksheet itself.

Any ideas?
 
C

Charles Maxson

I resort to using a custom function in VBA like so:


Function WorksheetName()
WorksheetName = Application.Caller.Parent.Name
End Function

Then in any cell, I enter the formula:

=WorksheetName()
 
D

David McRitchie

Hi Purshina,
See top of
Pathname in headings, footers, and cells
http://www.mvps.org/dmcritchie/excel/pathname.htm

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

The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).

I see you also have a VBA solution. The Worksheet solution will
be faster and more reliable (you don't have to run a macro).
 
F

Frank Kabel

Hi
try one of the following formulas (note: the workbook has to be save
before). Just use the formulas as they are shown (don't
replace 'filename' with anything else)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))
 
Top