I am opening an Excel 2000 workbook on Excel 2007. This workbook contains several worksheets with charts, shapes, formulas and defined names. When I save the workbook in Excel 2007, it gives me the message "A formula in this worksheet contains one or more invalid references". It really does nor matter what worksheet I'm on while I try to save the workbook. Excel does not pinpoint the error which is not at all useful. How can I find the source of this annoying message?
Home Ribbon > Editing tab > Find And Select dropdown > Selection Pane...
Click each chart currently displayed on the sheet, and rename it over in the pane in order to differentiate the currently displayed charts from the extras in the list.
Once you rename each current chart, you know the rest are all old unused objects, and delete them by selecting them in the pane and pressing delete.
Do this for each sheet with chart objects, and save the workbook. The errors should no longer appear.
- Proposed as answer by KyleGW Monday, May 18, 2009 7:13 PM
Hi, I have the same problem in Excel 2007 (file created with Excel 2007), did you find a solution? Thanks in advance. -<M>.
Finally found the problem, it was in one of my data series within one of the file's charts.
I isolated the problem by:
1. Created a backup copy of the workbook and worked on it.
2. Deleted sheet by sheet and saved the file each time till the error stopped appearing - Found the problematic sheet with the chart.
3. Delete series by series in the chart till I found the problematic one and fixed the invalid reference within it.
4. Fixed the problem in the original file.
hope this helps.
- Proposed as answer by JamesP77 Tuesday, February 04, 2014 10:55 PM
Gosh this is an irritating problem. Its a really shame about this issue - another bug is "negative or zero values can not be plotted on log charts" keeps popping up all the time in a similarly unwarrarented manner.
Good tips. I had exactly the same message but in Excel 2010.
Main difference here is that the error message only seems to pop up when you are on the tab with the error in. So there's no need to start deleting tabs etc. I may be wrong here but that's certainly what I found.
Next up is that the error did indeed relate to the charts that I had on that tab. Again, that removes the need to start looking at all the numbers and formulas that you have on the page.
Finally, and this one was bizarre, it turned out that for some reason Excel had overlaid 2 additional charts onto the ones that I had placed there. It was these 2 charts that had the errors in. All I had to do was delete the 2 charts and as if by magic, there was one chart left underneath that had no errors in it.
Seemed like a bizarre solution in the end, but that's how it worked for me. Find the tab (easy), look at your charts and have faith in deleting them. You may find as I did that you have to delete them twice until you reveal your original error free version hidden underneath them.
I have this same annoying problem with a spreadsheet created in Excel 2003, imported and saved in Excel 2010 as a .xlsm file. There are NO charts in it, only many data sheets with no formulas. I have even gone sheet-by-sheet and checked for errors, with none found. I have searched for errors with the 'goto' utility. There are no links to external data. There is a lot of vba code, but it compiles fine. I am at a loss.
PROBLEM:Same problem with Excel Starter 2010. Every 20 seconds or so a pop-up warning would appear saying "A formula in this worksheet contains one or more invalid references". It would only pop-up on the worksheet with the error except when I would try saving. It would also pop-up when I was viewing a cell with a formula and would press enter.
For me, it was a problem with a chart in the specified worksheet.
For each chart you have in your sheet, right click it, move it to a new sheet, and check if the problem still exists in the original sheet. This will pinpoint which chart the error is in.
For me, simply moving the chart to a new worksheet fixed the problem. I then moved it right back.
If you can save without it popping up, problem solved.
I just came across this problem and a friend asked me to help. Here is what I discovered:
Firstly I am using Excel 2010, so my guess is this is not something new as people had this problem for sometime now. Secondly my friend created a spreadsheet with eleven charts and duplicated that spreadsheet several times in the workbook.
What did I discover:
To make sense of the data we the users create Excel gives every object a name which is usually sequential. Thus if you create 6 charts, charts will be named chart 1, chart 2... Because of this duplication, there were 12 sheets in the workbook that contained an object called chart 1 to chart 11. Excel now appears to be confused with several objects having the same name! While the file will save and you are able to function normally you will get this error until you resolve the issue.
KyleGW steps to show the objects in the sheet will help, but you now need to give each object a different name in order to truly correct this particular issue.
I hope that helps!
I had this problem in Excel 2007 SP3, and I found no hidden charts. There was only one chart and all references in it were valid whenever the error message happened.
I was deleting the range referenced, then replacing it with new data. But the problem never occurred when the range was deleted. Instead it was always at some random later time when the reference was valid.
Solution: Delete the chart's seriescollections before deleting the range. Then after the new data is in the range, recreate the chart's seriescollections.
THANK YOU MOK2011. This worked for me. My document contained only one sheet. That sheet contained only one chart. I added a temporary sheet, and then cut the chart from the original sheet and pasted it to the temporary sheet. Immediately, the problem was gone. I saved the file, moved the chart back to the original sheet, deleted the temporary sheet, and lived happily ever after.