How to display Sheetname in cell

[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.

 

[Ken Wright]

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

File must have been saved once.

 

[Peo Sjoblom]

You can use this formula

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

 

[Dave Smith]

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