using a worksheet tab name as a cell entry.

[bacon]

=RIGHT(CELL("filename"),LEN(CELL("filename"))-
MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"),
ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))


I liftd this code off the Ms site, but it returns the
entire file name pleus the tab name. I tried monkeying
around with it, (replaced the '\' with a ']' and this got
me the tab name.
If I use this in a work book, with multi sheets, all cells
refer to the last sheet opened, upon refresh.

simple example:
three work sheets in one book, A,B and C.
if all sheets have this formual in, C will be displayed on
all of them as the name, if sheet c is the one open. how
can I get it to distinguish between sheets? is there an
anchor point ($) I need to add to some bit?

 

[Peo Sjoblom]

The workbook has to be saved first and you need to put a cell reference in
the cell function

CELL("filename",A1)

you can also simplify the formula to

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

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Frank Kabel]

Hi
see:
http://www.xldynamic.com/source/xld.xlFAQ0002.html