Dynamic Range Scatter Chart

[jimbolo21]

I'm well and truely stuck on this one and was wondering if anyone had any ideas.

I have three tables. Each one contains data i use as a separate data series on a scatter graph. (I have them set up dynamically so they expand and everything works nicely.)

The problem I have is if one of the tables is blank with no records in it. It screws up my chart. It shows on the chart as a maker equal to zero and returns a message "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, named range, and cell reference."

Any ideas how go about setting up the scatter graph so that it will ignore blank data series ?

 

[MDCurry]

I believe all excel charts (column charts do) will ignore #n/a. Can you substitute the blanks for #N/A? If so you could then use conditional formats to "hide" (change the font to white) where the cell is #N/A

 

[jimbolo21]

Thanks for the suggestion.

#N/A works with blank data points on the scatter graph but doesn't seem to work with a blank data series.

It still wants to add one data point to the graph with a value of zero.