How to display Sheetname in cell

  • Thread starter Thread starter Joseph Saunders
  • Start date Start date
J

Joseph Saunders

Is there a way to display the name of the sheet within a
specific cell? For example if the sheetname is Jan-04, can
I have that same name display in cell A1 using a formula?
Thanks very much.
 
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34)

File must have been saved once.
 
You can use this formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)
 
There's got to be a simpler way to do this but some like this might help:

Add this function:

Public Function SheetName(s As Range) As Variant
SheetName = s.Worksheet.Name
End Function

Then in your sheet use it like this:

=SheetName('Jan-04'!A1)

At first glance this doesn't seem like much of a gain but Excel will
automatically update the reference in the formula when the sheetname is
changed or when the sheet or formula is copied. So if you're copying the
formula or renaming the sheet, this is a step in the right direction.

Maybe the gurus will chime in with a better way.

Oh, yeah. This will return text and not a date. If you need a date do
this instead:

=DateValue(SheetName('Jan-04'!A1))

HTH

Dave
 
Back
Top