A Formula in this sheet contains one or more invalid references

NYQuicksale used Ask the Experts™
I've workbook which contains graphs and a lot of formulas, the graphs are dynamic, the data range contains a lot of #N/A, to avoid plotting blanks on charts.
I'm getting a msg time and again in my excel 2007 that a formula in this sheet contains one or more invalid references. Verify that formula has valid path,workbook,range name and cell reference.

I want to get rid of this msg.

Any quick and working help in this regard shall be highly appreciated
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Excel 2007/2010?
Go to you Formulas tab, and on the "Formula Auditing" group, you have an option for "Error Checking". Open this and you "Trace Error" option there that should lead you to the formula that has the problem.



it does not show any error, i've done it for the whole sheet many times, but no error
There is a reference to an external reference which has errored out.

Try searching for a [ sign in the entire workbook and get rid of that reference.

If you dont find it try cycling through all graph elements.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


is there any way which can stop this reference thing, i've already tried everything, as i told u my workbook contains alot of N/A, intentional ones to avoid blank data to get plotted. i guess that is the reason, how can i avoid it?
No it has got nothing to do with the #NA

search for a [  or for ref and get rid of it


this is not working, there's a vba code application.displayalerts = false
can i associate it with worksheet_calculate? and also at the time of saving the file,
this code must work somehow to avoid displayalerts, can someone assisst, is it actually possible?

Is it possible for you to post the file here?
If your file cannot be publiced then try eliminating.

Delete one sheet - save it - open it. If it goes away then that sheet is the culprit. Now delete charts one at a time (you might try 5 at a time if there are a lot of them). Next start deleting ranges 1, 2 or 5 columns at a time till you track down the culprit.
Can you, if found, post the culprit for the benefit of others?


I actually found the solution of it way before your reply, but since your reply contains similar answers,dats y i rated you as the solution.
This msg appears because of;
A)(THE MAIN ROUTE CAUSE OF THE ERROR MSG, IF YOUR WORKBOOK CONTAINS CHARTS,THEN FOR SURE THIS IS THE REASON OF THIS MSG)The charts that are built from dynamic name ranges and when the name range becomes blank, because of no data. Also to note that the chart that become victim of this error msg are the line charts with lots of blanks/NA#(to avoid plotting zero's on chart). I didn't see this error on the bar charts, may be the reason is that instead of blanks/NA# the range of bar charts contained Dash(-). Once the blank chart is deleted, the error disappears, so the solution is to delete the chart(which nonody would be wanting to do,because its a dynamic one,somtimes no data and sometimes lots of data).
B)the named ranges that contain #REF, once they are deleted, the msg dont appear.
Thanks for the feedback and thanks for the grade.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial