Dynamic ranges in charts

I have a chart showing year to date figures. My underlying data picks up the date of the report and then shows month and ytd figures based on the month. To show the ytd figure, I have used the following formula:

=IF(L6="ok",SUM($C38:L38),"")

L6 is showing that the column is in ytd (date less than report date). The Sum gives me ytd if the column is in period and then returns blank if not.

The problem is that the chart is taking the blank as a zero, instead of a blank and rather than ending the chart at that point (which was my intention), is dropping the line to zero.

Any help please?

Thanks

Robert
Upgrading XP to Vista is like replacing Bill Clinton with Dubbya

Originally Posted by DonkeyOte

You could perhaps try either:

a) adapt the range such that future columns are ignored altogether (do not appear on axis)

or

b) change your formula replacing the Null ("") with NA() (datapoints will not plot)

Thanks - the NA() works perfectly (although the data looks nasty with "#N/A" all over the place.

I don't understand how to acheive the former suggestion - how do you do this in a standard graph?

Thanks again - really helpful

Robert