Excel 2010: Assigning X/Y Values to Chart Series

I need to plot two sets of values against one another.  The values are located in various uncontiguous cells.  There are a very large number of cells.

I tried using the Union() function to create the two ranges of cells that I could then assign to .Xvalues and .Values.  This method supports creating very large combinations of cells, however, after unioning the cells, Excel no longer remembers the order in which I added cells to the range so when I run the plot, the lines do not connect in the correct order from point to point.  Most of my data is organized in rows, but Excel seems to pick data in order by column first.

I also tried creating strings like this:  "Sheet1!$A1:$A1,Sheet1$B5:$B5, ..."   and assigning those to .Xvalues and .Values.  This method respects the order of the cells so the lines plot as expected, but it generates an error when the string gets around 8000 characters so it won't work for large data sets.

Is there some other way to assign a large number of uncontiguous cells to .Xvalues and .Values for plotting?

I am running Excel 2010 on Windows 7.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
have you considered trying X Y chart labeler http://www.appspro.com/Utilities/ChartLabeler.htm   it is free
Saqib Husain, SyedEngineerCommented:
Why don't you try  building a new range with the data organized as required?
You might create a new (Hidden) sheet.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@professorjimjam, that add-in adds labels to already existing charts. The question is about a very different issue.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

ProfessorJimJamMicrosoft Excel ExpertCommented:
thanks teylyn.
one approach -  dynamic named ranges to hold the individual blocks then a superset of named ranges to concatenate the individual blocks into the value sets you need?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
koughdurAuthor Commented:

I did some simple experiments and it appears your method will probably work.  I shouldn't take me that long to integrate your method into my code and see if it works.

koughdurAuthor Commented:
This worked!  Most of my data goes across in rows so I created a named range for each row of data.  Then I created a master named range combining all of those named ranges for each of my X and Y data sets.  Then I set .XValues and .Values to the master ranges and VOILA it worked!

All my plot lines are being connected in the correct order.

Thanks a million.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.