A while ago I bumped into a very promising [Wayback/Archive] CELL function – Microsoft Support which exposes all sorts of interesting information on an Excel WorkSheet cell including address and filename.
But then this “disclaimer” threw me off:
Note: Formulas that use CELL have language-specific argument values and will return errors if calculated using a different language version of Excel. For example, if you create a formula containing CELL while using the Czech version of Excel, that formula will return an error if the workbook is opened using the French version. If it is important for others to open your workbook using different language versions of Excel, consider either using alternative functions or allowing others to save local copies in which they revise the CELL arguments to match their language.
It means the CELL function is only useful if the spreadsheet containing it will only ever be used in a single language: say goodbye to portability.
That’s a real bummer as it would have simplified formulas like =ADDRESS(ROW(E7), COLUMN(E7)) into =CELL("address", E7) both resulting $E$7.
The big problem is that “consider either using alternative functions” is hardly possible as many of the functions have no alternative, for instance using the CELL function is the only way to get the name of the current worksheet (prepended by the filename) as =CELL("filename") returns Macintosh HD:Users:jeroenp:Downloads:[Workbook1.xlsx]Sheet1.
Note however:
Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved.
The basic syntax of it is CELL(info_type, [reference]), where info_type and some of the return values being language dependent:
Read the rest of this entry »
The Wiert Corner - irregular stream of stuff 