Hi guys

is there a way to use the worksheet name as the contents of a cell?
As in, I would like my A1 cell to contain the name of the current Sheet, is
that possible, I've tried searching but no luck.

Many thanks!!

Elaine

Elaine, try this,
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)



File MUST be saved for this to work
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"Elaine" wrote in message
...
Hi guys

is there a way to use the worksheet name as the contents of a cell?
As in, I would like my A1 cell to contain the name of the current Sheet,
is that possible, I've tried searching but no luck.

Many thanks!!

Elaine

Paul B...I compile one formula from the forum and it goes like this...
=MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,LEN(CELL("filename",A 2))-FIND("]",CELL("filename",A2)))

what is the difference....i dont understand....pls. explain....

"Paul B" wrote:

Elaine, try this,
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)



File MUST be saved for this to work
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"Elaine" wrote in message
...
Hi guys

is there a way to use the worksheet name as the contents of a cell?
As in, I would like my A1 cell to contain the name of the current Sheet,
is that possible, I've tried searching but no luck.

Many thanks!!

Elaine

Dear Paul

That's exactly what I was looking for
Many many thanks!

Elaine

"Paul B" wrote in message
...
Elaine, try this,
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)



File MUST be saved for this to work
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"Elaine" wrote in message
...
Hi guys

is there a way to use the worksheet name as the contents of a cell?
As in, I would like my A1 cell to contain the name of the current Sheet,
is that possible, I've tried searching but no luck.

Many thanks!!

Elaine

try the following to see if it helps.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Inportance: Your worksheet file should be named, if not , you will get error
in it)

Regards

A HKSAR Friend

"Elaine" .gbl...
Hi guys

is there a way to use the worksheet name as the contents of a cell?
As in, I would like my A1 cell to contain the name of the current Sheet,
is that possible, I've tried searching but no luck.

Many thanks!!

Elaine

Another way to do it is to call a macro as follows:

Option Explicit

Function SheetName(Optional CellOnWorksheet As Range) As String
Application.Volatile ' update on changes
On Error Resume Next
If CellOnWorksheet Is Nothing Then
Set CellOnWorksheet = Application.Caller
End If
SheetName = "Error!"
SheetName = CellOnWorksheet.Parent.Name
End Function


HTHs

Martin Fishlock.


"Music Lover" wrote in message
...
try the following to see if it helps.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Inportance: Your worksheet file should be named, if not , you will get
error in it)

Regards

A HKSAR Friend

"Elaine"
.gbl...
Hi guys

is there a way to use the worksheet name as the contents of a cell?
As in, I would like my A1 cell to contain the name of the current Sheet,
is that possible, I've tried searching but no luck.

Many thanks!!

Elaine