A Formula in this sheet contains one or more invalid references
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
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
ASKER
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.
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.
ASKER
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
search for a [ or for ref and get rid of it
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you, if found, post the culprit for the benefit of others?
ASKER
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.
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.
Saqib
Saqib
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.
jppinto