Excel 2007 : Series disappear

**BlueSkyBlue** · 05-16-2011, 12:01 PM

I regularly produce charts from a data range on a spreadsheet using F11 and then using my saved templates to format. I then copy the chart to another excel workbook to which I addd other charts similarly produced from other spreadsheets and data ranges. The person I email these to started to get a REF!! error and series information disappearing from charts (about the time we strted to send in xlsx rather than xls) - so I looked online and saw it was necessary to preserve the data 'as is' at that point in time - which is in any case what is requried in this instance. So I edit links (in the data tab) and break links before saving and emailing. This seemed to work perfectly emailing in house - but externally on receipt we now have whole series disappearing from the charts! Unlike before where REF!! appered in the legend - the correct series label appears - but no corresponding series line/data on the chart. Very frustrating - it's there is I look at the file - it's there if I pdf it - but disappears on emailing (or indeed on saving to a lower version eg 2003)

HELP PLEASE!!!!

**MarvinP** · 05-16-2011, 12:41 PM

Hi BlueSkyBlue an welcome to the forum.
This sounds like a networking problem where you data is coming from a drive on the net. When someone else in your workgroup gets the workbook, the drive and data can be found. When you send it (email) it outside of your workgroup the pointer to the drive/data is unresolved and you get the #Ref message.

I'd be looking at formulas that point to data outside the workbook itself and try to limit them to data inside the workbook. Click on View Formulas to see if you find pointers to data outside the current workbook. If there are, can this data be included in the workbook you email to others?

**BlueSkyBlue** · 05-16-2011, 01:53 PM

Thank You Marvin - I really appreciate your trying to get your head round this! The source data though is in a workbook on my PC. I use it to create the graphs but then once the graph is copied to the new workbook I edit links to break the relationship and stop excel looking to update the link/data on future openings. There are no forumlas in the finished document that I email that relate to outside the workbook itself - so that's why I can't understand it...

Any further thoughts would be v much apprecited...

**MarvinP** · 05-16-2011, 03:49 PM

In Pivot Tables and Pivot Charts the data for the table/chart is cept in memory only. If the data changes you need to do a "REFRESH" to update the Pivot to reflect the data on the sheet. The Refresh updates the memory copy of the data and redoes the Pivot.

When you say you "edit the links" you may not be breaking the relationship between the memory data. After some searching the term I was looking for was Pivot Cache. Search on it Pivot Cache and msdn to learn more. I hope this helps.