This is one way --
This is a discussion on Activesheet.name as a formula not code? within the Excel Questions forums, part of the Question Forums category; I wish to return the name of the sheet in cell D1. I know how to do this in code. ...
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.
Thanks that does the trick. Now as to why does that work???
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.
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.
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.