Glad it worked.
--
David Biddulph

Les wrote:
Hi David,

I apologize for not being as specific as I could have been. Whats
interesting is that although I initially saved the workbook as test
it didnt show the file name when I cut and pasted your code. When I
just now opened it to see about providing you better info as to what
I was getting it worked FINE. It seems that although I saved it
initially when I tried it I may have had to close down Excel and
started it up again for that change to take place!! It works just as
you told me it would. Thanks so much for your help and all of you
who helped. I will be more specific in the future when discussing a
problem.

Happy New Year,

Les

"David Biddulph" wrote:

Well, Les, so far you are winning this year's prize for the least
helpful description of a problem. "is not working " is not very
specific. What value did the formula return? What value did you
expect it to return? Are you sure that you've got my formula
exactly as I wrote it? Perhaps you should copy the formula from
your formula bar and paste it back here so that we can see it?

The time when the CELL function doesn't work (returning the #VALUE!
error) is when you haven't saved the workbook, but you say that the
workbook is named test so it sounds as if the workbook has been
saved.
Another reason for getting a #VALUE! error from the cell function
would be if the info_type argument of the function is invalid, for
example if you had mistyped the word "filename".

Have you looked at the results for the CELL function from Excel help
to see if that helps?
If you are convinced that
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
doesn't return the sheet name for you, what does the formula
=CELL("filename",A1)
return for you?
--
David Biddulph

Les wrote:
Hi David,

I dont kow but your cod is not working for some reason. What have
is a workbok called test that has 3default sheets (sheet1, sheet2,
sheet3). Now what I want to be able to do is the following. If I
am in sheet1 I want to list the sheet name in lets say cell b2. I
I go to sheet2 I will have your cde there 2 but this time I want
cell b2 on sheet2 to say sheet2 etc. Basically depending on what
sheet I am in, all sheets will have your code there to start with,
so that I can use the sheet name in some of my spreadsheet.

Hope I made myself clear, sorry for the confusion.

Thx Les

"David Biddulph" wrote:

Sorry, that should have been
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

"Les" wrote in message
...
Hi,

Is there an easy way fro me to grab the name of the active sheet
I am in. ie
if I am in sheet9 I want to be able to use the name sheet9 in
code etc? Thanks in advane,

Les