I have an Excel chart. Originally it was a column chart with two data
series, comparing averages from two different data populations.
Recently I added a third series which put a dotted horizontal line
across the column chart to show the overall average.

When I added that horizontal line I named it "Overall Average = [x]"
and that name immediately showed up in the chart legend at the bottom.
Which was great.

The way this project works is: All of the data for the chart is in a
separate worksheet called "Data." I wipe out all the data and save the
workbook to be used as a template. The data is supplied by an Access
database. I copy all the data from Access and paste it into the
template's "Data" worksheet. And, voila!, we have pretty charts.

The problem is that when I paste the data into the worksheet the third
line of my legend, the one that says "Overall Average =[x]", suddenly
vanishes. The legend lines for the other two series remain.

What's happening here? How can I get it to keep all three lines of the
legend?

Thanks.

On Apr 13, 4:18*pm, Evan Cater <evanca...@gmail.com> wrote:
[snip]
> The problem is that when I paste the data into the worksheet the third
> line of my legend, the one that says "Overall Average =[x]", suddenly
> vanishes. The legend lines for the other two series remain.
>
> What's happening here? How can I get it to keep all three lines of the
> legend?

If you look at the formula for the data series that should
be named "Overall Average =[x]" is there an entry in the
name part of the formula.

=SERIES(Sheet1!$C$3,Sheet1!$C$4:$C$6,Sheet1!$H$4:$H$6,5)

If your paste has stomped on this formula you might see some
kind of error thing there. #Ref or something.

If there is a valid formula, then look at where it points. Does that
cell contain the name as it is supposed to be? Maybe your
cut-and-paste operation stomped on it.
Socks

No, the series is fine. The first argument in the SERIES formula (the
one that defines the chart Name) is pointing to the cell that says
"Overall Average =[x]".

Turns out the problem is that when new data is pasted in, Excel
repaints the legend and it puts so much space between lines that my
third line is forced off the screen. Any way to get it to retain my
formatting so that it all fits on the screen?


On Apr 14, 1:09*pm, Puppet_Sock <puppet_s...@hotmail.com> wrote:
> On Apr 13, 4:18*pm, Evan Cater <evanca...@gmail.com> wrote:
> [snip]
>
> > The problem is that when I paste the data into the worksheet the third
> > line of my legend, the one that says "Overall Average =[x]", suddenly
> > vanishes. The legend lines for the other two series remain.
>
> > What's happening here? How can I get it to keep all three lines of the
> > legend?
>
> If you look at the formula for the data series that should
> be named "Overall Average =[x]" is there an entry in the
> name part of the formula.
>
> =SERIES(Sheet1!$C$3,Sheet1!$C$4:$C$6,Sheet1!$H$4:$H$6,5)
>
> If your paste has stomped on this formula you might see some
> kind of error thing there. #Ref or something.
>
> If there is a valid formula, then look at where it points. Does that
> cell contain the name as it is supposed to be? Maybe your
> cut-and-paste operation stomped on it.
> Socks