Newby Question

J

Jim@gems

Can someone please help???


I am trying to write a piece of code which will use the sheet name as
value in a cell


example:


Sheet1(Sales)

Sheet 2( Purchases)


Therefore sheet1 Cell C2 value = Sheet1 name
And
Sheet2 Cell C2 value = Sheet2 nam
 
B

BrianB

Try this :-
=RIGHT(CELL("Filename"),LEN(CELL("Filename"))-FIND("]",CELL("Filename"),1))

which will only return the sheet that the cell is in.
Try =CELL("Filename") on its own to see why
 
J

Jim@gems

No Joy Brian


Jim


Sheet1(Cell(C2).Value) = Sheet1("Sales")

keeps debugging at sub or fuction not define
 
A

Andy B

Jim

When Brian wrote "Filename" that's exactly what he meant!! He didn't mean
the name of your file he meant "Filename".

Andy.
 
K

Ken Wright

And also note that the file needs to have been saved for it to work

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Andy B said:
Jim

When Brian wrote "Filename" that's exactly what he meant!! He didn't mean
the name of your file he meant "Filename".

Andy.
Click to expand...
 
J

Jim@gems

Sorry guys but i think i am thick

I'm just not getting my head around this at all



i've attached a sheet to show what i mean.


Sorry for being a pai

+----------------------------------------------------------------
| Attachment filename: book3.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=358872
+----------------------------------------------------------------
 
A

Andy B

Jim

Make sure the worksheet has been saved.

In a blank cell type:

=cell("filename")

Andy.
 
J

Jim@gems

Thanks to everyone.


I was looking to do this in vb.

but the solution given helps


Many thank
 
D

Dave Peterson

See one more reply to your first post.

Jim@gems said:
Can someone please help???

I am trying to write a piece of code which will use the sheet name as a
value in a cell

example:

Sheet1(Sales)

Sheet 2( Purchases)

Therefore sheet1 Cell C2 value = Sheet1 name
And
Sheet2 Cell C2 value = Sheet2 name
Click to expand...
 
Top