Use

=RIGHT(CELL("filename",'other sheet name'!A1),LEN(CELL("filename"'other
sheet name'!A1))-SEARCH("]",CELL("filename"'other sheet name'!A1)))

or a but simpler

=MID(CELL("filename",'other sheet
name'!A1),SEARCH("]",CELL("filename",'other sheet name'!A1))+1,99)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Lenard" wrote in message
...
I've been browsing through previous posts to find a way to show the tab
name
of those several sheets to be displayed in a separate sheet.

Tabs named Test1, Test2 etc names to be displayed in say "Final" wksht in
cell a1 for Test1, cell a5 for Test2 etc.

Tried this but it kept displaying "Final" wksht tab name and variants of
code.
=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
Any help be great.