That formula will only work if the workbook has been saved.



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




--

Dave Peterson