charts disappear when saved on different sheet

**Pasha81** · 11-05-2009, 05:37 PM

Hi I have a workboook.
On Sheets 1 and 2 I have lot of data. On Sheet 3 I have a report with charts realting to data in sheets 1 & 2. When I save sheet 3 as a new workbook and email it to someone else the charts dont work

Any idea how to address something like this?

Thanks

**Andy Pope** · 11-06-2009, 05:15 AM

What does "charts dont work" actually mean?

Can you post an example of one of these charts.

**jsmath22** · 11-06-2009, 12:15 PM

Anytime you have a chart, the corresponding data must be in the same workbook. Not sure if you have image capturing software but you could use SnagIt or Camtasia to take a picture of it and send it to someone. I think SangIt has a 30 day free trial.

**Andy Pope** · 11-06-2009, 12:28 PM

Anytime you have a chart, the corresponding data must be in the same workbook.

That's not true.

I just did this test. New workbook. in A1:A4 enter the values 1,2,3,4
Create a column chart. Copy chart only to new workbook and save.
Delete original workbook.

My chart has columns with a series formula of

=SERIES(,,[Book1]Sheet1!$A$2:$A$5,1)

When I reopen the workbook I get a dialog warning about links, which you would expect. If I ignore the links I see my chart still.

Now if you want to edit the chart in any way you have a problem depending upon the amount of data in the chart. For my small test I can edit the series formula and using F9 convert range references to array values.

=SERIES(,{1,2,3,4},{1,2,3,4},1)

The problem is the series formula is limited to 1024 characters.

**Pasha81** · 11-09-2009, 07:39 AM

Hi guys thanks for attempting to solve my problem here, and sorry I was away over the weekend and didnt get a chance to look into this.

I've attached a sheet here to show what I mean when i say the chart doesnt work, when in a separate workbook

I will try it again( what Andy suggested)

btw, is it better to do the whole chart by macro?

Many thanks

**Andy Pope** · 11-09-2009, 07:48 AM

In you workbook I see,

Sheet 2 - empty
Sheet 3 - empty
Report - 7 charts linked to various workbooks.

The charts labelled Volatility, Index & Margin% and Drawdown appear empty.
But that is because.

Volatility is a line chart with 1 data point.
Index & margin & is a area and line combination. Again with only 1 data point.
Drawdown is an area chart with 1 data point.

I think you need to double check the named ranges that these charts are based upon.

**Pasha81** · 11-09-2009, 01:41 PM

Thanks Andy, will do. I was thinking maybe i'll use vba to create the charts. I'll record a macro then figure it out.
Before that ofcourse I'll check out the named ranges, its possible there was something wrong there....its supposed to have way more than 1 data point, there were atleast 300.