Sunday, February 06, 2005

Using the sheet name in an Excel file

Here is the formula I use in a file to get the sheet name.

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

This always works because of the syntax that Excel uses in referencing.

Path[filename.xls]'sheet name'!cell reference

The '' marks around the sheet name only appear if the sheet name has certain special characters (eg space) in the name.
When ever you link a cell to another file the square brackets [] will always be around the file name.
When ever you link a to a cell on another sheet the sheet name and cell reference are always separated by the !.

The only exceptions are links to named ranges which can be on different sheets (and even different files. However the full syntax will be listed in the named range dialog box (CTRL F3).


Posted by: Jethro | 11:29 PM |