Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Invalid Names when Opening Workbook.
Written by Allen Wyatt (last updated October 8, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Vivek received a workbook from a colleague and when he tries to open it he gets an error: "One or more invalid names were detected in this workbook. Those invalid names have been changed to #REF." This means he can't see some of the calculations. The same workbook is okay when sent to another colleague, so Vivek wonders why the workbook won't work for him but it does for his colleague.
This problem is probably due to one of two things. First, it is possible that your colleague's system has some add-ins loaded that allow the workbook to function properly, but you don't have them. The solution is to display the add-ins that are being loaded on your colleague's system. Write them on a piece of paper, start Excel on your system, and then make sure you have the same add-ins loaded.
The other possibility is that the workbook includes formulas that are linking to external data from other workbooks. It is entirely possible that your colleague's system has access to those other workbooks (either locally on his system or over a network) and you don't have access to them. The only way to determine this, again, is from your colleague's system. Have him load the workbook and look for external links it may contain. Figure out where that external data is located, and then make sure you have access to that same data on your system.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12349) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Invalid Names when Opening Workbook.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
If you need to stuff the current workbook's filename and path into a cell or a header or footer, you'll appreciate the ...
Discover MoreNeed to save a workbook in more than one location? Here's a handy macro that can save your workbook in lots of different ...
Discover MoreImagine how painful it would be if every time you started Excel it tried to load all the files in your root directory? ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-10-10 10:57:06
J. Woolley
My Excel Toolbox includes the following dynamic array function to list available add-ins with four columns (Add-In, Installed, Open, Path):
=ListAddIns()
First pick Developer > Excel Add-ins to open and close the Add-ins dialog, which initializes ListAddIns.
The following dynamic array function lists external links:
=ListExLinks([SkipReference],[SkipHeader])
The result is similar to the Data > Queries & Connections > Edit Links dialog minus its Update column, but ListExLinks optionally adds a Reference column to identify cells that contain a formula referencing the link.
In older versions of Excel these functions can be used with the SpillArray function like this:
=SpillArray(List...(...))
See https://sites.google.com/view/MyExcelToolbox/
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments