Let's say you want to add information to a spreadsheet report that confirms the location of a workbook and worksheet so you can quickly track and identify it. There are several ways you can do this task.
Insert the current file name, its full path, and the name of the active worksheet
Type or paste the following formula in the cell in which you want to display the current file name with its full path and the name of the current worksheet:
=CELL("filename")
Insert the current file name and the name of the active worksheet
Type or paste the following formula as an array formula to display the current file name and active worksheet name:
=RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))
Notes:
-
To enter a formula as an array formula, press CTRL+SHIFT+ENTER.
-
The formula returns the name of the worksheet as long as the worksheet has been saved at least once. If you use this formula on an unsaved worksheet, the formula cell will remain blank until you save the worksheet.
Insert the current file name only
Type or paste the following formula to insert the name of the current file in a cell:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
Note: If you use this formula in an unsaved worksheet, you will see the error #VALUE! in the cell. When you save the worksheet, the error is replaced by the file name.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
