Activesheet.name as a formula not code?

[KniteMare]

I wish to return the name of the sheet in cell D1. I know how to do this in code.
But, is it possible to do with just a formula? =(SheetName) or some such.

I wish to use this as a template for my 18 facilities and have the sheet refer to D1 where ever the facility name needs to be. This way I just have to type it one time.

Any help or questions are appreciated.

KniteMare

 

[just_jon]

This is one way --

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[KniteMare]

Thanks that does the trick. Now as to why does that work???
KniteMare


EDIT Follows
OK I got it.

Return all the text to the right of the ] char.
By finding the position of the ] and subtracting that from the total length
and returning that many char from the right.

Thanks so much for the help.
End EDIT
Knite Mare

 

[just_jon]

The CELL function is an information function, one of whose arguments can be "filename" -- see the help file for the others. [2nd arg = reference of 1st arg]

However, by itself, CELL... returns the Full name, including path/file/sheet, as in --

C:\Documents and Settings\Administrator\My Documents\MrExcel\[FUNCTIONS.xls]Sheetname reference

In order to strip out just the sheetname, we want the RIGHT most portion only, of a length equal to the length of the entire above string less the position number of the closing "]" around the filename: in this case, the LEN function returns 95, the FIND function returns 76, leaving 19.

 

[KniteMare]

Right, I looked up each piece of the function in my trusty MicroSoft Excel 97 Worksheet Function Reference, AFTER you pointed me in the correct direction. Without your assistance I would never have thought to Look up the CELL function as a starting point for this procedure.
KniteMare

 

[just_jon]

Congrats with the refernce material! We like seeing that!