Thread: 'Smart' charts?

I have a very simple line chart with two rows of data, and columns (dates) stretching out several months. Without having to constantly revise the source data, is there a way to have the chart automatically ignore cells with empty data?

For example, if every column is a day in July, I wouldn't want the chart to continue to the 31st.... only to today... and so on...

Thanks in advance!!

Something like this perhaps? I used a named range in the chart. You can right click on the chart, click "Select Data..." and edit that series to see how to enter a named range in a chart. Essentially, you use ='Book3.xls'!Quantity (name I chose for the named range). HTH

THANK YOU!! I'm sure this will do the trick!

So I got to work... sort of... It doesn't seem to like forumae being present in the cells. I've got an 'IF' function that keeps the cells blank until there's data above (calculates and graphs a percentage change).

When I clear the contents of the cell, your trick works well. When the formula is there (even though blank), it doesn't...

Any suggestions? Thanks regardless!

It works for me when I have a formula that returns a blank, but one way around this issue is to change the named range formula from what I had it as to

=OFFSET(Sheet1!$A$3,0,0,1,31-COUNTBLANK(Sheet1!$A$3:$AE$3))

, since COUNTBLANK usually does a better job of recognizing blanks that are caused by formulas than the rest of Excel.

Ok... your original fix works great! I didn't pay attention the 'refers to' part of your definition of the named range... operator error...

PERFECT!

Thanks!!