Sheet Name to Fill Cell

R

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
 
P

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
 
M

Max

RoadKill said:
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?
Click to expand...

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
 
R

Rick Rothstein \(MVP - VB\)

If the Excel file has been saved...

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

Rick
 
R

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