Charts - combined column and line chart - removing gaps in data

[JLarks]

Hello,

I've been searching for a while, but I haven't seen something quite as complex as my question...

I'm trying to create a chart that combines columns and scatter/lines. I have an x-axis that is years, and I have a primary and secondary y-axis. I'm wanting to plot one data set to each y-axis, one as a column and the other as a scatter/line.

The secondary data has a continuous data set over years, but the primary data has only occasional years of data. The primary data keeps filling gaps with 0's and the option to "connect data points with line" is grey'd out, so I can't choose this. I don't really want to create a million points by manually interpolating between the points either (as it will then give me a million points instead of my 8 points).

Does anyone know how I can make this work?

Thanks,
Jordan

 

[thisoldman]

I can't duplicate your problem. Without seeing a few lines of representative data it's difficult to suggest a solution.

I have a hunch that the blank cells in your data set are not truly empty. Do they contain formulas?