Insert Worksheet name into cell A1

[Leslie]

Is there anyway (without using VBA) that I can
automatically have the name of a worksheet appear in cell
A1?

 

[Dan E]

=CELL("filename")
gives
C:\WINDOWS\Desktop\[Book2.xls]Sheet2

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
gives
Book2.xls

=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
gives
Sheet2

Dan E

 

[Leslie]

That's great, Dan, but when I put that same formula in
cell A1 on different sheets, all the a1 cells change to
that sheet's name. How can I get it to stay different
for each sheet?

-----Original Message-----
=CELL("filename")
gives
C:\WINDOWS\Desktop\[Book2.xls]Sheet2

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL ("filename"))-1)

gives
Book2.xls

=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH

("]",CELL("filename")))
gives
Sheet2

Dan E

"Leslie" <[email protected]> wrote in

message news:[email protected]...

 

[Dan E]

Leslie,

The CELL("filename") formula will always display the name
of the workbook/sheet in which calculations were last done
I don't know of any other ways to insert the sheet name?

Dan E

That's great, Dan, but when I put that same formula in
cell A1 on different sheets, all the a1 cells change to
that sheet's name. How can I get it to stay different
for each sheet?

-----Original Message-----
=CELL("filename")
gives
C:\WINDOWS\Desktop\[Book2.xls]Sheet2

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL ("filename"))-1)

gives
Book2.xls

=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH

("]",CELL("filename")))
gives
Sheet2

Dan E

"Leslie" <[email protected]> wrote in

message news:[email protected]...

.

 

[Dave Peterson]

Change each reference from

cell("filename")
to
cell("filename",a1)

(any old cell on the worksheet will work)

That's great, Dan, but when I put that same formula in
cell A1 on different sheets, all the a1 cells change to
that sheet's name. How can I get it to stay different
for each sheet?

-----Original Message-----
=CELL("filename")
gives
C:\WINDOWS\Desktop\[Book2.xls]Sheet2

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL ("filename"))-1)

gives
Book2.xls

=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH

("]",CELL("filename")))
gives
Sheet2

Dan E

"Leslie" <[email protected]> wrote in

message news:[email protected]...

.