Excel Chart Vertical Lines Corrupting?

[Ken Black]

Does anyone know why vertical lines plotted with a XY series on a line
chart would become corrupted? If so, is there a fix or way to prevent
this?

I update 6 workbooks monthly and each has about 40 line charts. We
added vertical lines to all of the charts 6 months ago using the
technique in John Walkenbach's "Excel Charts" book. We adjust the
vertical lines by changing the values in the A column of a single
table each month:

A | B
------------------------------------
| XY Series | |
------------------------------------
| | Pre 2004 |
------------------------------------
| 58 | 0 |
------------------------------------
| 58 | 100 |
------------------------------------

Normally this works fine, but each month as we add one more month's
worth of data, several of the chart's vertical lines don't correctly
respond to the adjusting of the values in the table. We've added a
second table, so we could have an alternate series value for the
vertical lines. This works for some charts, but a few charts have
vertical lines that won't move correctly (move barely, and
unpredictably left or right). We've deleted the vertical line series,
and then re-added it, but still the same problem.

The only fix that seems to work is to delete and rebuild the entire
chart, but this is too time consuming. And then the next month we
have more charts with the same problem.

The problem seems to occur only on charts that have a mixed number of
data points in their series, for instance, they may have 6 series with
7 data points (the same number as are in the x-category axis), but 4
series with only 5 data points. (The charts are somewhat complex,
using additional series for "conditional charting" plotting.).

If anyone knows a "fix" for this problem, I would appreciate
assistance.

Ken Black

 

[Jon Peltier]

Hi Ken -

No fix, perhaps, but something to check. The line series in the chart
are plotted on the primary axis, presumably. Are the XY series (the
vertical lines) also plotted on the primary axis? If not, they could be
using a hidden secondary X axis, which is scaling independently from the
primary.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Ken Black]

Jon,

Thanks for the reply. Actually, the vertical lines are plotted on the
secondary axis purposely. With the line charts plotted on the primary
axis. I checked and they are on the appropriate axis.

Any other ideas that might help?

Thank you,
Ken Black

 

[Jon Peltier]

Ken -

Do the primary and secondary axes update in synch? If one changes and
the other does not, you will see this kind of behavior.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Ken Black]

Jon,

I apologize for not checking back on this sooner. I would say that
they don't update in synch. Typically I will update the data set for
the primary axis, then after I determine how the vertical line should
move, I will change the numbers in the table that all the secondary
axis (vertical lines) point to.

So I would not be able to update them in synch.

(If I understand your question).

Thank you,
Ken