I am attempting to create a chart with a dynamic data series. Each series in the chart comes from an absolute range, but only a certain amount of that range may have data, and the rest will be #N/A
.
The problem is that the chart sticks all of the #N/A
cells in as values instead of ignoring them. I have worked around it by using named dynamic ranges (i.e. Insert > Name > Define), but that is extremely inefficient, as each chart has 4 dynamic series, and I must make 25 of these charts.
Are there any other solutions that allow me to specify a range, as normal, for a data series, but tell the chart to ignore all "#N/A" or blank cells?