formula to get sheet name
 

hi,

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


thanks,


N.S.

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("filenam e"),1+FIND({"*","]"},SUBSTITUTE(CELL("filename"),"\","*",LEN(CELL("f ilename"))-LEN(SUBSTITUTE(CELL("filename"),"\","")))),255),". xls",""))

Regards,
KL



"Nigel" wrote in message
...
hi,

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


thanks,


N.S.

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

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

--
Regards,
Dave
<!--


"Nigel" wrote:

hi,

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


thanks,


N.S.

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


"Nigel" wrote in message
...
hi,

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


thanks,


N.S.