Put WORKSHEET TAB name into a CELL ?

  • Thread starter Thread starter www.ttdown.com
  • Start date Start date
W

www.ttdown.com

Hello the Group,

Please help with a method of putting the name of the particular
worksheet TAB into a cell in that worksheet.

I am sure I saw how to do this here once but I can't find it - maybe
too long ago.

This time I won't forget.

Can someone please help?

magic
Darryl using Excel 2002 & XP
 
Hi Darryl!

The formula used is:



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


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi
try the following formulas (work after you saved the workbook the first
time. Don't replace "filename" with anything, leave the formulas as
they are)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))
 
Hello the Group,

Please help with a method of putting the name of the particular
worksheet TAB into a cell in that worksheet.

I am sure I saw how to do this here once but I can't find it - maybe
too long ago.

This time I won't forget.

Can someone please help?

magic
Darryl using Excel 2002 & XP
Click to expand...

If the workbook has been saved, then:

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

If the workbook has not been saved, then you could use a User Defined Function
written in VBA. Something like:

====================
Function SheetTab() As String
SheetTab = ThisWorkbook.ActiveSheet.Name
End Function
===================


--ron
 
Thanks Norman for the reply on screen almost before I posted the
query. BUT....

But I can't get it to work!

To recap, I want to put a formula in a cell (say A1) that will show
tha TAB name from below.

Sorry to be so dense. Could you explain a bit more?

magic
Darry using Excel 2002 & XP
 
Did you try Norman's formula? It works if the workbook has been
saved. What results did you get?





www.ttdown.com said:
Thanks Norman for the reply on screen almost before I posted the
query. BUT....

But I can't get it to work!

To recap, I want to put a formula in a cell (say A1) that will show
tha TAB name from below.

Sorry to be so dense. Could you explain a bit more?

magic
Darry using Excel 2002 & XP

Hi Darryl!

The formula used is:



=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Click to expand...
Click to expand...
 
THANK YOU to Norman, Frank & Ron.

I forgot you have to save to get the CELL(filename function to work.

I will try again - and I would like to try the VBA solution as well.

Gonna get right to it now.

Thanks all.

Darryl
 
Hi Darryl!

Sorry but I should have said that the file has to be saved first. You
could get round that requirement but I'd always recommend frequent
saving anyway.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks for your interest Chip.

Of course the darn thing works now thar stupid me saved the file.

Thanks again EVERYBODY.

Darryl

Did you try Norman's formula? It works if the workbook has been
saved. What results did you get?





www.ttdown.com said:
Thanks Norman for the reply on screen almost before I posted the
query. BUT....

But I can't get it to work!

To recap, I want to put a formula in a cell (say A1) that will show
tha TAB name from below.

Sorry to be so dense. Could you explain a bit more?

magic
Darry using Excel 2002 & XP

Hi Darryl!

The formula used is:



=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Click to expand...
Click to expand...
Click to expand...
 
Should work as you that is exactly what it does. Make sure there
is nothing in front of the equal sign. What does it do instead of work?
Perhaps you have calculation turned off, or are in formula view.
 
Wasn't fast enough to delete my reply from the outbox. Of course
it was save the file first.
 
Back
Top