MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Worksheet name in a cell?


Posted by Steve-Dave on January 29, 2002 7:39 AM

I can't find this any where! Is there a simple way to show the name of the worksheet in a cell?


Posted by Mark W. on January 29, 2002 7:53 AM

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

...but, you must Save your workbook before this
formula will work.

Posted by JohnG on January 29, 2002 7:57 AM

Sub WbookNameInCell()
Workbooks("Book2").Worksheets(1).Range("A1").Value = ThisWorkbook.Name
End Sub

Posted by Ian Mac on January 29, 2002 8:11 AM

Mark....


Agian you amaze me, but why the A1, I tried it with just:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

and it's fine for me

Comments

Ian Mac

Posted by Mark W. on January 29, 2002 8:17 AM

Interesting...

The help topic for CELL worksheet function shows
the CELL worksheet function's 2nd argument in
bold characters which normally indicates that the
argument NOT optional. Evidently, the help topic
is incorrect. : =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) : ...but, you must Save your workbook before this

Posted by Ian Mac on January 29, 2002 8:27 AM

Even more Interesting...and an answer!!

I've just placed the same formula in three different sheets AND different cells, the result is they ALL say the same thing (the last sheet where the cell with the formula was activated/entered). BUT when I used your formula it obviously referenced a cell in the same sheet and hey presto! the sheet name.

Ian Mac

Posted by Aladin Akyurek on January 29, 2002 10:21 AM

Cell ref ensures anchoring. [NT]

Posted by Hecate on January 29, 2002 4:31 PM

Another way .....


Define two names :-.

Name : sh
Refers to : GET.DOCUMENT(76)

Name : wb
Refers to : GET.DOCUMENT(88)


To return the sheet name, enter in any cell :-
=RIGHT(sh,LEN(sh)-LEN(wb)-2)


Also, you can get the Workbook name from =wb
And, the [Workbookname]Sheetname from =sh