Sheet Name to Fill Cell

[RoadKill]

Good afternoon good people,

I was just wondering if it is possible to fill a field with a sheet name. So
if my sheet is named John Doe, can cell A1 be prefilled with that name?

Thank you

 

[PCLIVE]

Your workbook has to be saved in order for this to work.

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

HTH,
Paul

 

[Max]

I was just wondering if it is possible to fill a field with a sheet name. So
if my sheet is named John Doe, can cell A1 be prefilled with that name?

The book must be saved first

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

Test it out:
In any sheet, in any cell, put: =WSN,
and that cell will return the sheet's name

---

 

[Rick Rothstein \(MVP - VB\)]

If the Excel file has been saved...

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

Rick

 

[RoadKill]

Thanks everyone. Some genius created a workbook with a ton of sheets with the
name of the individual being scored listed dozens of times on each sheet. So
any shortcut is good.