XL: Macroless Method to Display the File Name of a Worksheet

• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for OS/2, version 3.0
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, if you want to create a formula that displays the file name of a worksheet in a cell but you do not want to use a macro, use the appropriate following method.

For example, to return the file name of a file with the path C:\EXCEL\DATA\TEST.XLS (if you are using Microsoft Excel for Windows) or HD:Microsoft Excel:Data Files:Test (if you are using Microsoft Excel for the Macintosh), the following formulas return TEST.XLS or Test.

If you are using Microsoft Excel 5.0 or later, the formulas in this article return the name of both the workbook and the active sheet.

MORE INFORMATION

Microsoft Excel for Windows or Microsoft Excel for OS/2

To display the name of the current worksheet, enter 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)))))

NOTE: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+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, you receive a #VALUE! error value.

Microsoft Excel for the Macintosh

To display the name of the current worksheet, enter the following formula as an array

   =RIGHT(CELL("filename"),LEN(CELL("filename"))-
      MAX(IF(NOT(ISERR(SEARCH(":",CELL("filename"),
      ROW(1:255)))),SEARCH(":",CELL("filename"),ROW(1:255)))))

NOTE: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

REFERENCES

"Function Reference," version 4.0, Pages 46, 239, 252, 377 "Function Reference," version 3.0, Pages 26, 137, 200, 208