formula to get sheet name

N

Nigel

hi,

can a formula retreive a worksheet name or does it need to be macro based?


thanks,


N.S.
 
K

KL

Hi Nigel,

This formula that takes care of the possible situation whereby you have only
one sheet in the workbook and its name is the same as the workbook's. Note:
CELL("filename") will only work if the file has been saved at least once and
if the file is opened in a different language system, the argument
"filename" will need to be changed manually to the corresponding word (e.g.
in Spanish "nombrearchivo"):

=LOOKUP(REPT("z",255),SUBSTITUTE(MID(CELL("filename"),1+FIND({"*","]"},SUBSTITUTE(CELL("filename"),"\","*",LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\","")))),255),".xls",""))

Regards,
KL
 
D

David Billigmeier

Copy the following code (Verbatim) into any cell (make sure the book is saved
first):

=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
 
C

Chip Pearson

Nigel,

Try the following formula.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

The file must have been saved to disk in order for this to work.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top