Forum Discussion
Notification of error in formula reference, but can't find error
I have a kinda complex workbook -- lots of simple formulas, multiple sheets and charts, but nothing too crazy. I keep getting an error message about a problem with a formula reference. I've gone through the charts and all the formulas I see. I also did the Formulas --> Error Checking --> Error Checking thing and can't find anything. A few of my formulas divide by 0 (waiting for data to be entered), would that trigger the error message? It reads,"Excel found a problem with one of more formula references in this worksheet. Chech that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct"
What else can I do?
Try looking for the error in Conditional Formatting > Manage Rules. A PITA as you have to look through each rule for an error, since it won't have any indication it's incorrect. Plus can only do a sheet at a time. But generally if you can't find an error when Excel says its there, that's the place to look.
Also, check in Name Manager on the Formulas page, especially if you copied data from one workbook to another.
4 Replies
Run Workbook Analysis in the Inquire menu.
I have 365 Home and no Inquire add-in is available. …. "Inquire is only available in the Office Professional Plus and Office 365 Professional Plus editions. If you don't see an entry for Inquire Add-in in the COM Add-Ins dialog box, it's because either your edition of Office or Excel doesn't include it...."
Try looking for the error in Conditional Formatting > Manage Rules. A PITA as you have to look through each rule for an error, since it won't have any indication it's incorrect. Plus can only do a sheet at a time. But generally if you can't find an error when Excel says its there, that's the place to look.
Also, check in Name Manager on the Formulas page, especially if you copied data from one workbook to another.
Thank you Randy Birch! I will remember these ideas.
In the end, I closed the file and when I opened it about an hour later, the error message went away. I must have corrected the error during the search and the file just took a while to register it.
