large non contiguous data set

[bhofsetz]

Hello,
I am trying to make an XY scatter plot of a rather large non-contiguous data set. I am running into a problem in that the box to enter the X Values and Y Values is not large enough for the entire reference to the cells I want to chart.
They are all on the same sheet just typically (but not always) 7 cells apart.

For example the x values are on the Results sheet in cells B15, B18, B25....etc.
but to reference these I have to use:

=(Results!$B$15,Results!$B$18,Results!$B$25,Results!$B$31,Results!$B$37,Results!$B$44,Results!$B$50,Results!$B$56,Results!$B$63,Results!$B$69,Results!$B$75,Results!$B$82,Results!$B$88,Results!$B$94,Results!$B$101,Results!$B$107)

Is there a way to make this reference without having to repeat the " Results!$B " every time. All X data is on the same worksheet and in the same column and all Y data is on the same worksheet and in the same column.

Thanks for any suggestions.

[bhofsetz]

Does anyone know how to reference a non-contiguous range on the same sheet without having to continually repeat the sheet name? (see below for example)

[MrShorty]

I don't have a direct answer to the specific question you've asked, but it doesn't seem anyone else does either. I don't have a final solution, but here are some ideas just to get the discussion going.

1) What's in between the individual data points? If the stuff in between is empty, text, or otherwise different enough from the data of interest, you could reference the entire column and set up the chart to hide the unimportant data.

2) This is sort of along the same lines, but if the data in between the points to be plotted will interfere with the plot, use column D & E (or other empty columns) to extract the points to be plotted from B and C (=IF(A1="plot", b1,"")). Then reference columns D & E in your scatter plot. The "excess" points will all be plotted as 0,0, but, if that is outside of the actual range of interest, you can hide those points when formatting the axes. Alternatively, choose something other than "" for the value_if_false argument that would put those points well outside of the range you are plotting in.

3) If it were my spreadsheet, I would probably prefer to do something on another part of the spreadsheet or a new sheet where I could place the data to be plotted in a contiguous range. For example D1=B15, D2=B18, D3=B25, E1=C15, E2=C18, and so on. Then reference columns D and E in the chart.

Just some ideas. If anyone else has other ideas...

[bhofsetz]

Thanks for the suggestions Mr Shorty.

In response to your questions/suggestions

I was hoping I could plot directly from the original data source without having to rearrange it (into contiguous series). I have several large data sets that need to be plotted based on given criteria. Currently I have a macro that will generate the plots desired however if the data set is too large I run into the problem described.
Unless you can referece multiple cells on the same sheet with less redundancy I suppose I may have to rearrange the data before plotting.

can I change the required Y values of:

=(Results!$B$15,Results!$B$18,Results!$B$25,Results!$B$31,Results!$B$37,Results!$B$44,Results!$B$50, Results!$B$56,Results!$B$63,Results!$B$69,Results! $B$75,Results!$B$82,Results!$B$88,Results!$B$94,Re sults!$B$101,Results!$B$107)

to something like this, without the need to repeat the sheet name?

=(Results!($B$15,$B$18,$B$25,$B$31,$B$37,$B$44,$B$50,$B$56,$B$63,$B$69, $B$75,$B$82,$B$88,$B$94,$B$101,$B$107))

I've tried similar syntax but it doesn't want to work for me. Is there a different syntax that can be used to accomplish what I'm after?

Thanks again for any help.