Charts - Copy chart worksheet problems

[sparkwoodand21]

Hi,

I'm new to charting and having scanned the old posts I can't find any reference to the problem I'm experiencing.

I have set up a simple graph data sheet as follows:

There are 6 rows per data set (one header and five data series).

Each data series contains 14 columns (one series name and then one for each financial period).

I have 170 data sets to graph.

So I thought I'd create the first graph on a separate sheet and then copy this sheet 169 further times and then redirect the source data on each to the required data set on the graph data sheet. As i had carefully laid out the data on the graph data sheet to be equi-distant it is more straightforward than you might think)

Problem 1 - when I redirect the source data, Excel decides that the first 9 columns are in fact part of my data series name and therefore charts only Period 9 to 13 and gives me ridiculous series names!

This I have begun to fix by resetting each series (name and values) in the source data dialog box. This is getting painful particularly if I have to do it 152 more times!

What am i doing wrong or is this a bug in the programme?

Problem 2 - Whilst the first chart initially remains as I had set it with one of the data series starting at a value in period 8 and increasing to period 13 (i.e. without touching the X axis and starting at zero). The duplicated sheets (once corrected) start in period 7 at zero and then rise to the period 8 value. Whilst not being a huge problem it does detract from the point the graph is trying to make. I have set [tools - options - chart] to not show zero values. Interestingly, after I had amended about 10 charts the first one miraculously linked itself to the X axis at zero!

Again - what am i doing wrong?

If anyone can help out I would be grateful, else this weekend will be taken up creating 169 charts individually.

Thanks for reading,

S

 

[RalphA]

Hmm... Sounds as if a VBA guru could give you the answer!

 

[Jon Peltier]

1. Excel finds 9 blanks in the top of the first column of the data range (assuming you're plotting by column), so it decides they are not part of the X data, and treats the top 9 cells of each other column as the series names.

2. I think there's still some confusion between you and Excel about the data range. Make the data range like this:

   A  B  C  D  E
a  1  2  3  4  5
b  2  3  4  5  6
c  3  4  5  6  7
d  4  5  6  7  8
e  5  6  7  8  9

The top left cell being blank tells Excel that the top row is series names and the left column is X values (they can be numeric, I used letters in the illustration). If you have a label in the top left cell, Excel may not correctly guess what you were intending.