inserting worksheet name in cell using function

I have found a formula on the web help site to
automatically insert a worksheet name into a cell of the
worksheet. I have several worksheets and wish to
identify each worksheet automatically in a cell of each
worksheet. When I use the formula it initially brings in
the correct worksheet name. However, when I select
another worksheet the prior name appears in the selected
cell.

Perhaps I need to change something in my formula or this
is a problem that I can't correct. What is your
suggestion?

The following formula is what I am using in each
worksheet.
=MID(CELL("filename"),SEARCH("[",CELL("filename"))
+18,SEARCH("]",CELL("filename"))-2 )

Perhaps there is a simpler way to insert the worksheet
name into a cell. Something like; =cell("worksheet"),
or =cell("filename,-path"). I haven't been able to find
a solution.

Please advise.

1. You have to save the workbook first
2. You need to put in a cell reference or
else it will return the active sheet's name

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


will do it

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

wrote in message
...
I have found a formula on the web help site to
automatically insert a worksheet name into a cell of the
worksheet. I have several worksheets and wish to
identify each worksheet automatically in a cell of each
worksheet. When I use the formula it initially brings in
the correct worksheet name. However, when I select
another worksheet the prior name appears in the selected
cell.

Perhaps I need to change something in my formula or this
is a problem that I can't correct. What is your
suggestion?

The following formula is what I am using in each
worksheet.
=MID(CELL("filename"),SEARCH("[",CELL("filename"))
+18,SEARCH("]",CELL("filename"))-2 )

Perhaps there is a simpler way to insert the worksheet
name into a cell. Something like; =cell("worksheet"),
or =cell("filename,-path"). I haven't been able to find
a solution.

Please advise.

Alternate method.......

User Defined Function(which is slower than the builtin that Peo provides), but
easier to enter and does not require the workbook to be saved first.

Function ShtName(Optional ByVal rng As Range) As String
Application.Volatile
If rng Is Nothing Then Set rng = Application.Caller
ShtName = rng.Parent.Name
End Function

Gord Dibben Excel MVP

On Mon, 24 May 2004 20:26:34 -0400, "Peo Sjoblom" wrote:

1. You have to save the workbook first
2. You need to put in a cell reference or
else it will return the active sheet's name

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


will do it