Written by Allen Wyatt (last updated July 12, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
Many people use the CELL worksheet function to return the name of the current Excel workbook. A common way to use the function is as follows:
=CELL("filename")
Using the CELL function in this manner is fine, provided you only have one workbook open at a time. If you open more than one, then this usage can cause problems. Why? Because when used this way, CELL returns the name of the currently active workbook, not the workbook in which the formula is used.
To always return the name of the workbook in which CELL is used (sometimes called the "parent workbook"), you must alter the formula just a bit:
=CELL("filename", A1)
By adding a cell reference as the second parameter in the function, you are telling Excel that you want the name of the file containing that cell reference. In other words, CELL will return the name of the file in which cell A1 of the current worksheet is located. (You can also provide any other cell reference in place of A1, if more appropriate.)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2047) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Getting the Name of the Parent Workbook.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Cells can store all sorts of information that can be formatted and displayed in a myriad of ways. If you want to quickly ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-07-24 14:00:44
Hi Allen,
I've been following your blog for months and always appreciate the quick insightful tips for Excel. I'm always looking to improve my excel skills and was not aware to add "A1" to the cell filename formula. I typically add the file name in the bottom left footer of my document but sometimes the path string is too long and runs into my middle footer. Placing the file name in the body of the worksheet allows you to see the path and lets you format this information. I find that the footer is tricky to format since you need to use the Print Preview feature in order to see the actual final layout of the text.
You are one of the most consistent and helpful Excel Blogger and I truly appreciate all of your tips - you have made me a better user!
Best
-Krist
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments