setting a cell to Blank or Nontext or Null

[Troy]

My dilemma: I must produce line charts where the data
points refer to the days of the month, but there is not
always valid data for every day. I want formulas to link
to source data for the entire month to avoid having to 10-
key everything, but not drop the chart line down to 0 when
the formula detects no valid data for a particular day.

Example: A cell formula in the plot range for a chart is
=if(iserror('[datasource.xls]tab1'!A25/'[datasource.xls]
tab1'!A26),"",'[datasource.xls]tab1'!A25/'[datasource.xls]
tab1'!A26). This formula makes the value if true resolve
to "" or a cell with empty text. In the line chart, this
still plots as 0. However, I want it to be a blank cell
so the chart can be set to NOT plot empty cells.

I don't have a choice on the chart type (i.e. change it to
a bar chart). Any good solutions beyond that?

 

[Tim Otero]

Hi Troy,

I believe what you are looking for is a dynamic chart. John Walkenbach has
good info here (Update charts automatically when you enter new data):

http://www.j-walk.com/ss/excel/usertips/tip053.htm

and Jon Peltier has good info here (Dynamic and interactive charts):

http://www.geocities.com/jonpeltier/Excel/Charts/index.html#hdrDyno

tim