In excel graphics is there a way to softcode the location of the data (and the legend) so one graph format can be used for several data sets

I have time series data to graph.   There are say 20 rows of data, one for each item, and a row containing a date series, which is common to all the data.   I want to be able to specify which row to plot and have the plot show that row.  I do not want a different graph for each row or one graph for all the rows.   Is there a way to do this?
LVL 1
dma70Asked:
Who is Participating?
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.

Glenn RayExcel VBA DeveloperCommented:
Sure.  You would do two things:
1) Set up an input cell using data validation to let you choose the category/row to plot.
2) Create a dynamic named range that will return the correct row of data for the selected value in step 1.

In this example, there are two sheets:  Data & Plot.  "Data" has a table of values, 20 groups with one value per day of a month (dates in row 1, columns B:AE).  "Plot" has a data-validated cell in A2 allowing the the user to pick the group to plot.  It also has the resultant chart.

A named range - PlotRow - exists with the following reference:
=OFFSET(Data!$A$1,MATCH(Plot!$A$2,Data!$A$2:$A$21,0),1,1,30)
It will return the range of values for whatever is chosen in cell A2 on the "Plot" sheet.

The chart was created as a regular bar chart pointing to one sample row.  However, the range of series values was changed to this:
='EE-DynamicDataPlot.xlsx'!PlotRow
* One must specify the workbook name along with the dynamic range name in order for this to work. *

Once completed, the user can select any group name from the pick list and the chart will automatically plot the related data.

Example file attached.

Regards,
-Glenn
EE-DynamicDataPlot.xlsx
0

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
dma70Author Commented:
Very elegant.   I had come up with a method where I specified a row to contain the data that was plotted and my pulldown copied the target data to the "plotting row", each time I wanted to plot a different data set.   Yours does the same but I will learn more from it.    Thanks for all the time it took to perfect this.
0
Glenn RayExcel VBA DeveloperCommented:
You're welcome.  I had actually considered your method (using a set of VLOOKUPS) but realized I could use a dynamic range name to achieve the same without needing an extra, dedicated "plotting row."

-Glenn
0
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
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.