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
141 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
  • 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 …

785 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