Solved

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

Posted on 2014-09-03
3
147 Views
Last Modified: 2014-09-03
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?
0
Comment
Question by:dma70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40302051
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
 
LVL 1

Author Closing Comment

by:dma70
ID: 40302075
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40302081
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question