Hi,
I searched and nothing quite fit. I have 12 sheets, one for each month. I have a chart sheet and a table sheet that directly reference these sheets and the totals.
Totals are calculated as I enter data for the months. I also want the charts to calculate on the fly as the totals are entered.
For months that haven't been entered there is "0" for their totals. So January looks good then Feb (since I haven't started yet) the line just plummets to the zero and plots across. I want months that haven't been completed, not to chart. Or if you want, that are zero values, not to plot. It doesn't matter to me how it has to be done. Just have the line end at the month prior to the zero value.
And no, I really don't want to change the X or Y range every month for 14 different charts, although I guess that's a possibility.
I've tried changing the Cell Format to show a Blank if the cell contains a zero. The cells show blank but the chart still plots the zeros. It does this even if I change it on the orginating worksheet and the Chart Tables worksheet. I tried hiding the rows containing the zeros. Unfortunately, that's either my X or Y axis for the chart so it hides my months or Regions on the charts. That won't work. Finally, hiding the worksheets (would make sense!) doesn't hide the cells that are in the plot (?), so they will not make use of the "plot visible only" under Tools > Options. Let me know if you have some input and hopefully I'm overlooking something obvious.
I searched and nothing quite fit. I have 12 sheets, one for each month. I have a chart sheet and a table sheet that directly reference these sheets and the totals.
Totals are calculated as I enter data for the months. I also want the charts to calculate on the fly as the totals are entered.
For months that haven't been entered there is "0" for their totals. So January looks good then Feb (since I haven't started yet) the line just plummets to the zero and plots across. I want months that haven't been completed, not to chart. Or if you want, that are zero values, not to plot. It doesn't matter to me how it has to be done. Just have the line end at the month prior to the zero value.
And no, I really don't want to change the X or Y range every month for 14 different charts, although I guess that's a possibility.
I've tried changing the Cell Format to show a Blank if the cell contains a zero. The cells show blank but the chart still plots the zeros. It does this even if I change it on the orginating worksheet and the Chart Tables worksheet. I tried hiding the rows containing the zeros. Unfortunately, that's either my X or Y axis for the chart so it hides my months or Regions on the charts. That won't work. Finally, hiding the worksheets (would make sense!) doesn't hide the cells that are in the plot (?), so they will not make use of the "plot visible only" under Tools > Options. Let me know if you have some input and hopefully I'm overlooking something obvious.
