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
143 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

789 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