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
137 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
Comment Utility
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
 

Author Closing Comment

by:dma70
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Article by: dandraka
There are several quick shortcuts that can make your life easier in Microsoft Programs.  These simple tips and tricks will your work more productive and you faster at completing your tasks! MS Word (1) Creating Re-usable Scraps You can create s…
Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now