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

ID: Q99348

The information in this article applies to:

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

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)))))

where "filename" is literally the word "filename", in quotation marks.

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.

In Microsoft Excel 5.0 or later, the formulas above will return the name of both the workbook and the active sheet. To return the name of the file only, use the following formula:


   =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL
   ("filename"))-SEARCH("[",CELL("filename"))-1)

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)))))

where "filename" is literally the word "filename" in quotation marks.

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.

In Microsoft Excel 5.0 or later, the formulas above will return the name of both the workbook and the active sheet. To return the name of the file only, use the following formula:


   =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL
   ("filename"))-SEARCH("[",CELL("filename"))-1)

REFERENCES

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

Additional query words: XL98 XL97 XL7 XL5 XL4 XL3 macrosheet template howto

Keywords : xlformula
Version : WINDOWS:3.0,4.0,4.0a,5.0,5.0c,7.0,97; MACINTOSH:3.0,4.0,4.0a,5.0,98
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo