Can you put the Sheet Name into a cell?

hello Dan and welcome to the board
you can use the cell function, with "filename" as a parameter

=CELL("filename",A1)

hth

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

Consult the Excel Help topic for "CELL worksheet function".
This message was edited by Mark W. on 2002-06-25 14:30

On 2002-06-25 14:19, DanH wrote:
Hello all,

Wondering if there is a function that would put a sheet's name into a cell?

Thanks, Dan

Click to expand...

You might want to use SHEETNAME the Morefunc add-in, dowloadable at:

http://longre.free.fr/english/index.html

by entering in some cell in the target worksheet:

=SHEETNAME()

or use the following formulas with built-in functions:

In A1 in the target worksheet enter:

=CELL("filename",Sheet1!A1)

In B1 enter:

=RIGHT(A1,LEN(A1)-SEARCH("@#",SUBSTITUTE(A1,"]","@#",LEN(A1)-LEN(SUBSTITUTE(A1,"]","")))))

For no workbook name or filepath, the following works well, if your workbook is saved.

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

But, my personal favorite is to throw the following function in a normal module:<pre>
Function sheet()
Application.Volatile
sheet = Sheets(Application.Caller.Parent.Index).Name
End Function</pre>

Now use =sheet() on any sheet. The workbook does not have to be saved.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-25 14:34

Thanks everybody! That does the trick.

Dan

Another way, although I like Nate's function best, is to use this code in the WorkSheet module.

Private Sub Worksheet_Activate()
Range("$A$4").Value = Me.Name
End Sub