Automatic avoiding empty cell

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" wrote in message
oups.com...
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" wrote in message
oups.com...
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