Dynamic Scatter Plot

[LadyS]

Hi,

I am still a bit hazy when it comes to creating dynamic charts, so let me explain what I am trying to do (see attached worksheet):

I am trying to create a dynamic scatter chart of X,Y data based on selecting the range from a dropdown list. I have all my X and Y values in Columns B and C and their associated ID in Column A. The dropdown will let me choose one ID from Column A.

I think this is some sort of combination of named ranges, index, match, and offset, but I can't seem to put it all together. I already have the drop down list taken care of so I just need to know what formulas I need to input for the named ranges (Series1_X, Series1_Y, Series2_X, Series2_Y) so that the plot automatically plots Series 1 and 2 based on what samples I choose.

See the attachment for more details.

Any would be awesome!

[Glenn Kennedy]

Last post for the day. the smell of ribs coming from the kitchen is making me hungry...

Try this out & let me know. I'll be back in the UK's morning.

[LadyS]

Hi Glenn,

Thanks for your reply! I am not sure what you did here but it's not plotting all the points (for example all the B samples). Also, these X,Y row values are unique to each sample, and this solution mixes them all up. Sorry, I think I didn't do a great job explaining this detail.

I know an alternative with INDEX/MATCH; see the yellow box that I added, but the problem with this is that I have to keep dragging down as I add data.

The problem is my data set is HUGE down to 10000's of rows and keeps getting bigger. The attached was just a simplified example. I really don't want to keep dragging down a formula as data gets added and have a bunch of #N/A's cluttering the worksheet. I'd rather have the plot data be directly pulled from the data itself rather than a bunch of separate columns.

Hope this clarifies!

[Glenn Kennedy]

No, I think it was me not reading your instructions carefully. Anyway, it is simplified a bit. The #N/As are still there, but I have used Conditional Formatting to make their text colour = white. I have set up a dynamic dropdown box for your series names. You can add to it / subtract from it as you wish. You can hide this column, if you like. The raw data are (currently) set to have 20,000 rows (max) and BOTH the formula (and the accompanying scatterplot) to return each individual series, a maximum of 1000. In the first case, adjust the ranges in columns H & I to adjust; and in the second, just copy on down. It will get very slow, though.... Already, it takes 2.5 seconds to generate a new scatterplot when you change the series chosen.

Finally, these are array formulas. They need to be set with CTRL + SHIFT + ENTER. This will insert the curly braces & make them work. Don't type the curly braces yourself.

[LadyS]

Hi Glenn,

Thanks! I think this will work (we'll see about how slow it ends up). I know the maximum number of points for each selection will only be about 15-20 so hopefully it won't be as slow.

Thanks again!

[Glenn Kennedy]

OK. Thanks for the rep. Any further problems - just shout.

[LadyS]

Thanks. And if there's still any way to do this with named ranges, that would be even more perfect!!

[LadyS]

Hi,

I just wanted to post a follow-up because I figured out a way to make this plot with named ranges. See the attached.

Essentially I created two named ranges, one for the X values and one for Y values and here's their respective formulas:

X:
=OFFSET(A3, MATCH(C16, A3:A13, 0)-1, 1, COUNTIF(A3:A13, C16), 1)

Y:
=OFFSET(A3, MATCH(C16, A3:A13, 0)-1, 2, COUNTIF(A3:A13, C16), 1)

This is definitely much easier and faster. Hope this helps someone!