Ok here goes,

I have a spread sheet with column A having the names of 12 departments and
the next 12 columns are weeks of the year...now after each week has completed
I enter a sales figure for each department and each week. I have a sand
(area) chart and a line chart set up with the range being the all department
and all 12 weeks, the problem is that it is reading the next weeks column
(not filled out yet) as zero and plotting all future weeks at zero, this is
making my area chart fall off to zero - does not look good. How can I set
this up so that the chart goes up and then just stops at the latest sales
number instead of reading the next unfilled column as sales of zero? I hope
this is clear.

Also, since my weeks sales figures in all the columns are reading from
another sheet (through formulas) I thought it might be because of that..but i
tried leaving it both ways..just blank where I manually put the figure..and
leaving the formula there which states if zero = ""...neither way worked.

Help!!! and thank you

Hi Chris. I hope you are not getting crush by this idea. Well I think I
understand your problem. You have twelve departments, but they not always
have data, so some of them could be (cero), and you have your graph looking
funny, right? Well if I am reading ok, then your most useful, and adecuate
solution would be using Pivot tables in that last table and filtering out all
blanks, or cero values. At the same time you can create the graph and it will
adjust along with the changes. Let me know if this help you...

Argy

"chris" wrote:

Ok here goes,

I have a spread sheet with column A having the names of 12 departments and
the next 12 columns are weeks of the year...now after each week has completed
I enter a sales figure for each department and each week. I have a sand
(area) chart and a line chart set up with the range being the all department
and all 12 weeks, the problem is that it is reading the next weeks column
(not filled out yet) as zero and plotting all future weeks at zero, this is
making my area chart fall off to zero - does not look good. How can I set
this up so that the chart goes up and then just stops at the latest sales
number instead of reading the next unfilled column as sales of zero? I hope
this is clear.

Also, since my weeks sales figures in all the columns are reading from
another sheet (through formulas) I thought it might be because of that..but i
tried leaving it both ways..just blank where I manually put the figure..and
leaving the formula there which states if zero = ""...neither way worked.

Help!!! and thank you

Hi,

you need to use dynamic range names to control the series in your chart.
This site and others will give you details:

http://peltiertech.com/WordPress/200...t-source-data/

--
Thanks,
Shane Devenshire


"chris" wrote:

Ok here goes,

I have a spread sheet with column A having the names of 12 departments and
the next 12 columns are weeks of the year...now after each week has completed
I enter a sales figure for each department and each week. I have a sand
(area) chart and a line chart set up with the range being the all department
and all 12 weeks, the problem is that it is reading the next weeks column
(not filled out yet) as zero and plotting all future weeks at zero, this is
making my area chart fall off to zero - does not look good. How can I set
this up so that the chart goes up and then just stops at the latest sales
number instead of reading the next unfilled column as sales of zero? I hope
this is clear.

Also, since my weeks sales figures in all the columns are reading from
another sheet (through formulas) I thought it might be because of that..but i
tried leaving it both ways..just blank where I manually put the figure..and
leaving the formula there which states if zero = ""...neither way worked.

Help!!! and thank you