Chart, lines drop to zero when no data entered

[Tom]

Have several charts that get the data from a master data
sheet which gets its data from several other sheets, thus
a linked formula is involved. X axis is for Jan - Dec. I
only thus far have data for Jan - May. The lines on the
charts are fine for Jan - May, but since there is no data
in June - Dec, the line drops to zero. It make the
production chart look like the person suddenly stopped
working. I realize I can erase the formulas but this is
not acceptable in that several people, not excel
proficient, will be updating their own charts and printing
them. I want the line to stop if the formula totals '0'.

 

[Frank Kabel]

Hi
instead of displaying a zero in your data cells try displaying
=NA()
in these cases

 

[Jon Peltier]

In addition to Frank's suggestion, you could refine dynamic ranges in
your sheet, which only include the data you want, and plot these. I have
some links and examples on my web site:

http://peltiertech.com/Excel/Charts/Dynamics.html

The NA() trick doesn't work the same way on all chart types. For
example, in an area chart, you might as well use a zero, because the
value drops to the axis as in your chart.

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