To display the name of the current worksheet, type the following
formula as an array formula
=RIGHT(CELL("filename"),LEN(CELL("filename"))-
MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"),
ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))
where "filename" is literally the word "filename", in quotation marks.
NOTE: You must input this formula as an array formula. To input a formula as an array formula, press
CTRL+SHIFT+ENTER.
The formula returns the full path and worksheet name of the current workbook as long as the workbook has been saved at least once. If you use this formula in an unsaved workbook, the formula cell remains blank until you save the workbook.
To return the name of the workbook only, use the following formula:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
: You do not have to input this formula as an array formula.
If you use this formula on an unsaved worksheet, you receive the following error:
When you save the worksheet, the file name replaces the error.