Graph non-adjacent column data

[mgia0055]

I'm trying to generate a line graph using VBA that selects data from alternating columns. I've included a spreadsheet that contains data for staff allocation (Forecast and Actual) over time.

I'd like to be able to create a graph that plots just the forecast columns for the following rows:
"Total Staff"
"Total Allocation"
"Remaining availability"

Alternatively, I'd like to create a graph that plots a comparison of Forecast vs Actual rows:
"Total Staff"
"Total Allocation" and
"Remaining availability".

I'm having difficulties with understanding how to set the correct range.

I'm using the following code but it's throwing an error:

Please Login or Register  to view this content.

thank you for your help

[Andy Pope]

Even with the code fixed you will not be able to produce a chart as the series formula for the non contiguous range will be too long.

Instead use formula to build a contiguous range of chartable data.

[mgia0055]

Could you provide an example of how to "use a formulat to build a contiguous range of chartable data"?

Thanks

[Andy Pope]

I have done a few cells so you can see what I mean.

[mgia0055]

hmm, I see what you are getting at. Is there still another approach that I could implement without creating a new table of contiguous data, say by creating a number of seperate ranges and then graph.

What if I were to create 4 seperate ranges (one for the x axis, one for total staff, one for total allocation and one for remaining availability), could I then generate a graph that way?

[Andy Pope]

You might get away with 4 ranges it really depends on the sheet name and cell reference.
As I said the series formula has a finite text length of 1024 characters.