Charts go to zero on blank cells - only when linked to other workbook that is closed

[tonygoni]

Excel 2010

I have a number of line charts that are linked to data ranges in other workbooks. The ranges include a number of blank cells (future data to be dragged down weekly). These cells are actually blank, not formulas conditionally returning a blank. These blanks are displaying as zeroes on the chart only when the other workbooks are closed. When the workbooks are opened, the blanks display as blanks again on the chart. This is a problem that began recently...it used to work fine!

I have checked Select Data: Hidden and Empty Cells, and they are all set to "Show empty cells as Gaps." Changing it to "Connect data points with line" does not solve the issue.

Any ideas would be much appreciated!

 

[ejbeck1]

Excel 2007

I am having similar problem. I have line charts that the Data area goes to blanks as these blanks get new data everyday but just recently started to show the blank areas on the chart. So my area is A2:A2000,H2:H2000. Very simple chart Col. A is date and Col H is price. A line of data is added everyday but the chart is showing all the blanks.

I have checked Select Data: Hidden and Empty Cells, and they are all set to "Show empty cells as Gaps." Changing it to "Connect data points with line" does not solve the issue.

Look forward to suggestions.

Thanks