Need Formula for Getting Worksheet Name

[shade73]

Hey, I'm trying to insert the name of my worksheet into a cell. Does anyone know what formula that would be?

Also, if I wanted to pull from multiple worksheets
(say Sheet1 & Sheet2) and put it in Sheet3.. what is the formula to do that??

Thanks all!

 

[Greg Truby]

=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,LEN(CELL("filename",B1))-FIND("]",CELL("filename",B1)))

 

[MartinK]

Hi Greg,

do you need the B1 reference in this case (no matter which cell was changed last)?

=MID(CELL("filename"),1+FIND("]",CELL("filename")),LEN(CELL("Filename"))

seems to work as well...

Martin

 

[shade73]

wow, thanks that was fast! I would have NEVER figured that out!

 

[barry houdini]

Hi Greg,

do you need the B1 reference in this case (no matter which cell was changed last)?

=MID(CELL("filename"),1+FIND("]",CELL("filename")),LEN(CELL("Filename"))

seems to work as well...

Martin

I don't believe that will work in all cases, if the last cell you changed was on another sheet it will show the name of that sheet

 

[Greg Truby]

Martin,

Apparently not (i.e. yours appears to work just fine for me too). The formula I posted was just one I'd jotted down years ago and had handy. Actually, I could swear I saw a better take on this question using SUBSTITUTE() somehow but can't find it. In looking, I did find some other alternatives, including a cute wee UDF from Nate.

Edit - ah, Barry - I wish you'd have been quicker with that post! That's what I get for not thoroughly testing.

Regards,

 

[MartinK]

I don't believe that will work in all cases, if the last cell you changed was on another sheet it will show the name of that sheet

That's right Barry... thanks for making that clear. :D

Greg, didn't mean to be a hairsplitter, I am using the formula I posted very rarely and it worked for me so far. I was just curious and Barry hit the nail on the head.

regards
Martin

 

[Greg Truby]

No problem, Martin, I only use that formula once in a blue moon myself. I'm just glad Barry stopped by to keep me from mis-informing the OP and any future readers! So I guess we both are giving Barry a quick tip o' the cap! :wink:

 

[MartinK]

Right.


Martin

P.S. Although I guess your formula works. It's mine that does not.

 

[Aladin Akyurek]

Martin,

Apparently not (i.e. yours appears to work just fine for me too). The formula I posted was just one I'd jotted down years ago and had handy. Actually, I could swear I saw a better take on this question using SUBSTITUTE() somehow but can't find it. In looking, I did find some other alternatives, including a cute wee UDF from Nate.

Edit - ah, Barry - I wish you'd have been quicker with that post! That's what I get for not thoroughly testing.

Regards,

Now I'd propose:

=REPLACE(CELL("filename",A1),1,SEARCH("]",CELL("filename",A1)),"")