Upon saving my Excel sheet, I receive the following error message:
We found a problem with one or more formula references in this worksheet.
Check that the cell references, range names, defined names and links to other workbooks in your formulas are all correct.
“Error Checking” (under “Formulas” → “Formula editing”) returns
The error check is completed for the entire sheet.
What is the fastest way to find what is causing this problem?
Excel in Office 365 ProPlus, Windows 7.
35 Answers
The problem was caused by a chart with empty data ranges. This potential cause was mentioned in this forum.
2This is caused by empty values in named ranges/cells.
To debug, go to Formulas > Name Manager. Look for Named Ranges with an empty value. Either change the where the reference points (the "Refers to:" column) such that it pulls in a value, or delete the reference if it is not needed.
Be careful deleting named ranges, as you can cause some downstream #REF errors.
The issue may not be with the workbook that is generating the error.
In my case, I had another workbook open that a colleague sent me, which caused the error message to appear every time I added a row to any data table in any other workbook I had open. When I closed that other workbook, the error message stopped appearing in the other workbooks.
So, if you have multiple workbooks open, pinpoint the problematic workbook(s). If you know which workbook is the problem, you can follow the advice in some of the other answers.
This error occur from name manager (Dynamic range) mostly. if you have define any range and there is no data then it will come. please check you dynamic ranges and fill some data for test then it will resolve.
In my case, I had no named ranges but almost all of my sheets that had a chart on them would cause the error to appear once after first viewing the sheet. My workbook was in XLS format. Saving it in the more modern XLSX format made the problem go away (and quartered the file size in the process!).
