software

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 A14%
Company B18%
Company C9%
Company DN/A or 0
Company E18%

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/Amagput
=na()
in the cells

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

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

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

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

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