Wednesday, June 22, 2005

Using the CELL function in Excel

The CELL function can be used for filename identification on a spreadsheet by using the syntax =CELL("filename") This is easily entered on a sheet by hitting the fx (function) button on the toolbar and typing filename in the info type box and hitting enter.

However this may not always show the file name for the current file on which the formula is entered.
There is a reason for this found in the help files and I have added a solution.

The correct syntax is CELL(info_type,reference).
The reference field is the cell that you want information about. If omitted, information specified in info_type is returned for the last cell that was changed.
If you change a cell in a different file, then go to the file you have the formula in and print it, then it will display as the other file name, which is obviously incorrect.

The solution is to enter A1 in the reference field like this =CELL("filename",A1)

This is a good method for people using Excel versions prior to 2003 to display the full path of the file. In Excel 2003 there is an option in the print settings to put the path and file name into the footer or header in the Page Print settings. However in versions previous there was no path option.


Posted by: Jethro | 6:28 PM |