Thread: Graph References

Hi all,

I'm working on a sheet to track fitness progresses.
For some reason, one of the graphs I'm using, keeps telling me that some of the references are invalid. I chked the references but they are all valid. Any ideas...?

Hello, Which graph of the 4 is giving you this invalid reference? From my perspective, they all look OK to me. Even if I go to the "Source Data" for all four graphs, I see no issues. But, I don't know what other details that might be involved here, so please provide a little more info to help explain the problem. ... Thanks, sauerj

I guess it's the second.
Except for the dynamic names I used, there are no other details.
I tried to delete the second, make a copy of the first graph, and amended the details to fit the second. Then the problem shifted to the third graph. Did the same again and he problem shifted to the fourth. When I did the same for the last time, the problem once again was at the second graph

Hello, I looked thru the worksheet in detail. I saw a couple things for you to look at that might be causing the problems. Again, on my copy, all four charts look fine (no errors). But, as I worked with it, I could foresee where a couple things might cause chart issues.
1) First I UNHID all hidden columns so to see everything.
2) ROW 18 has key data, but these cells are invisible due to WHITE FONT. So, I increased ROW 18 row height to 15 and changed cells C18, J18, Q18, X18 from WHITE font color to AUTOMATIC color. ... In these cells, you should see numbers: 4, 2, 5, 2 (from left to right). These are the # of data points you want to show up on the charts.
3) IDEA #1: These numbers (4,2,5,2) are manually entered. If their value is GREATER than the # of associated entries in the LOG table (for this associated exercise), you will get #N/A errors in the OVERZICHT table and the chart line will go away. For example, change cell 'C18' from 4 to 8, and you will see what I mean (assuming chart #1 is working for you at startup). ... Maybe this is the problem on chart #2 (???). If not, continue.
4) IDEA #2: Right-click on chart #2, go to the "Source Data". Look at the contents of the "Values:" entry field. If this is not entered correctly, then this chart will fail. Make sure the exact typing of this entry is as follows: "= 'Name-of-File.xls'!BB" (without the outer quotes), and where Name-of-File = the exact name of your file. After downloading, it was Sport(1).xls on my PC. So, for me, this field is "= 'Sport(1).xls'!BB". ... This "BB" is a Range Name based on a formula. The other charts use range names: AA, CC, DD. All of them have the same formula structure (no errors there). But, I noticed that the "Name" of the 2nd Chart was "bb" (small case), while the other chart names were AA, CC, DD (all upper case). Not sure why this would make any difference, but maybe there is a connection.
5) Anyway, to completely eliminate any possible issue that might come up with such simple Range Names (AA, BB, CC, DD), I RENAMED these range names to (Chart1, Chart2, Chart3, Chart4), and changed the charts to match the new names.
I am attaching my TEST version for you review to see if Chart2 now works for you. It also includes all of the formatting changes per #1 & #2 above.
Sorry for long note ... hope this helps, sauerj

Thanks for helping Sauerj, but non of the ideas you came up with worked. The problem persists. I've no idea how to solve this...

It does not. Only after having saved it

As I could not find the answer here and no one isn't replying for a couple of days I decided to place the same question on another forum. This is the link http://www.mrexcel.com/forum/showthr...49#post2211549

Jon, Is it possible if you could make a Print Screen picture of the worksheet showing what the chart looks like with these error indications. Then, paste this picture into a DOC file and post on this site. ... Maybe this would help spur ideas (try this on both forums). ... I tried one more time to duplicate what you are seeing, and to no avail. This is a real puzzler. Everything looks good to me ??? ... sauerj

Jon,
* Answering your question, I get absoutely NO errors whatsoever.
* The file I am working with is the attachment from my previous post (2-7-2010, 4:21pm). I try to duplicate what you are seeing: I open it, then save it, then open the saved copy, then open & re-enter some of the cells. I NEVER get an error message.
* I have attached a jpg (page1, no errors) of what I see, showing NO errors. Notice that I am still in Excel 2003 (but doubt that makes any difference). It is interesting that moderator TEYLYN sees your same error. So, that rules out a lot of misc potentials.
* I notice that ALL your cell values equal the same for mine, except NO chart line on chart2, and the 'X' axis is missing the '23' point.
* Attachment "page2,error" shows the error message I get if I change cell 'J18' from 2 to 3. Same message as you get. But, you still have this cell as '2' so doesn't really explain anything. Just interesting that message is same.
* Attachment "page3,chart" shows chart2 source data. Since you said that you tried all of my previous suggestions, I assume yours looks like this (or similar), and so to no avail to fix problem.
* Attachment "page4,NameManager" shows list of all Range Names in file. Note that all names have similar formulas. These names are used in the 4 charts. No errors here. HOWEVER, since the error seems to deal with the chart (& not a cell formula) and since these Range Name Formulas are so "hidden", I really wonder if this isn't (in some wierd way) the source of the problem. ... NOTE: This "Range Name Manager" is a FREE Excel Add-in which is a GREAT tool that far exceeds Excel built-in Name Manager utility. I highly recommend installing this. (http://www.jkp-ads.com/officemarketplacenm-en.asp).
* Last attachment "page5,ErrorChecking" ... Note that I have the Formula Auditing toolbar docked at top,right. Now, after you have "OK'd" your error message. Please, turn on your formula auditing toolbar also, and click on the 'Error Checking' icon (the yellow diamond w/ red exclamation w/ check ... darkened one in my jpg). Notice the message I get when I click on this icon (checking complete, w/ no discovered errors). If you have a formula cell error, then this utlity should take you to that cell and tell you what the problem is. Holler back w/ any new developments.
* Hope some of this is of some help (as I'm sure that you are pretty frustrated with this seemingly 'simple' issue), sauerj

It seems that the problem only occurs in excel 2007.
I opened it on a computer with excel 2003 installed and... no error.