Hi,

I am making templates to draw chart (scatter charts and bar charts).
Scatter charts also have trendlines in them. At times I dont have value
for some of the items which are used to make these (scatter)charts.
This doesnot give a correct trendline since it assumes value as zero.
As such I have to manually change the data source and exclude items for
which i donot have a value.

Is there a solution such that the excel automaticallty doesnot take the
item (which doesnot have a value) to draw the chart.

e.g.
Margin
Company A 14%
Company B 18%
Company C 9%
Company D N/A or 0
Company E 18%

I would like excel not to take Company D in drawing the chart next time
it can be Company C since these values are coming from some other
sheet. There are a number of such charts in the template. Plz suggest
if it can be automized.

TIA/Amag

put
=na()
in the cells

"Amag" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I am making templates to draw chart (scatter charts and bar charts).
> Scatter charts also have trendlines in them. At times I dont have value
> for some of the items which are used to make these (scatter)charts.
> This doesnot give a correct trendline since it assumes value as zero.
> As such I have to manually change the data source and exclude items for
> which i donot have a value.
>
> Is there a solution such that the excel automaticallty doesnot take the
> item (which doesnot have a value) to draw the chart.
>
> e.g.
> Margin
> Company A 14%
> Company B 18%
> Company C 9%
> Company D N/A or 0
> Company E 18%
>
> I would like excel not to take Company D in drawing the chart next time
> it can be Company C since these values are coming from some other
> sheet. There are a number of such charts in the template. Plz suggest
> if it can be automized.
>
> TIA/Amag
>

So if D has no value, you want your categories to be A-B-C-E. Here are a
couple of old posts which describe macros that skip zeros and blanks in a
chart's source data range:

http://groups.google.com/group/micro...0c111832fb3337

http://groups.google.com/group/micro...209f1f0600a526

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

"Amag" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I am making templates to draw chart (scatter charts and bar charts).
> Scatter charts also have trendlines in them. At times I dont have value
> for some of the items which are used to make these (scatter)charts.
> This doesnot give a correct trendline since it assumes value as zero.
> As such I have to manually change the data source and exclude items for
> which i donot have a value.
>
> Is there a solution such that the excel automaticallty doesnot take the
> item (which doesnot have a value) to draw the chart.
>
> e.g.
> Margin
> Company A 14%
> Company B 18%
> Company C 9%
> Company D N/A or 0
> Company E 18%
>
> I would like excel not to take Company D in drawing the chart next time
> it can be Company C since these values are coming from some other
> sheet. There are a number of such charts in the template. Plz suggest
> if it can be automized.
>
> TIA/Amag
>

quite complicated ... lemme try ..tks anyways /Amag