Charting blank cells as 0 when using dynamic ranges

[tkos]

In an Excel 2000 workbook, I'm using dynamic ranges to define a cell range. I need to use dynamic ranges because the data in the spreadsheet changes each day and re-creating all of the charts manually would be too much work. A simple example of the problem is below
Sheet1
A B C
Date Color Price
1/1/2009 Blue 10
1/2/2009 Blue 15
1/3/2009 Red 8
1/4/2009 Yellow 9
1/5/2009 Blue 8
1/6/2009 Green 7

In the example above the named ranges are:
"Date"-=INDIRECT("Sheet1!$A$2:$A$" & COUNTA(Sheet1!$A:$A),TRUE)
"Color"-=INDIRECT("Sheet1!$b$2:$b$" & COUNTA(Sheet1!$A:$A),TRUE)
"Price"-=INDIRECT("Sheet1!$c$2:$c$" & COUNTA(Sheet1!$A:$A),TRUE)
"BluePrice"-=IF(Color="Blue",Price,NA())

When a chart is created using the "Date" & "BluePrice" ranges, the "#N/A" values are plotted as zeros. Is there any way to create a chart using these ranges without have the zeros plotted? I didn't run into this problem in previous versions of Excel.

I would prever not to place the "blueprice" formula on a work sheet because the spreadsheet and creating the chart that way. The workbook I'm working with has hundreds of charts on it and placing all of the formulas on a sheet within the workbook would slow things down considerably.

Thanks for any help

 

[Sal Paradise]

Select the chart.

Tools -> Options... -> [Chart] Tab ->

Active Chart -------------------------------------
Plot Empty Cells As (o) Not plotted (leave gaps)

-or-

(o) Interpolated

Depending on which you want.

 

[tkos]

Thanks for the reply Sal. It looks like I left out a key piece of information in my earlier post, I'm currently using Excel 2007. Your solution works perfectly in 2000, any ideas how to get it to work in 2007?

 

[Sal Paradise]

Not the foggiest idea. My apologies.