?
Solved

Dynamic Chart based on Month

Posted on 2011-09-12
6
Medium Priority
?
335 Views
Last Modified: 2012-05-12
Hi Experts,

I would like to request Experts help. How to make the Chart 1 and Chart 2 dynamically changed the value based on Month selection at Cell L38. Data in Chart 1 are extracted from Table 1 and Chart 2 data are from Table 2.

Hope Experts will help me create this dynamic chart based on month selection.
Chart.xls
0
Comment
Question by:Cartillo
[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
  • 2
  • 2
6 Comments
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1000 total points
ID: 36526588
Hi, Cartillo.

This seems like a good exercise for a couple helper columns off to the right.  The results group date/data together, based on month selected for the first chart, and part/data together, based on month selected for the second chart.

Once that was done, I just "selected" the data in Chart 1, pointing it to the helper columns, repeating the same for Chart 2.

I used HLOOKUP, INDEX, and MATCH functions to get what was needed.  These columns may be hidden, as needed.  Note, the data for chart1 has error handling.  For example in September there's only 30 days.  As a result, I made sure NA() was returned for the data as the chart would ignore data points with #N/A values.

See attached.

Enjoy!

Dave


Chart-r1.xls
0
 
LVL 50
ID: 36526876
Hello Cartillo,

for the first chart, you don't need helper columns. In the attached file, I defined three dynamic range names:


cht1Data      =OFFSET(cht1Labels,0,1)
cht1Labels      =INDEX(Report!$B$3:$J$3,MATCH(chtMonth,Report!$B$2:$J$2,0)):INDEX(Report!$B$3:$J$33,DAY(DATE(YEAR(Report!$L$38),MONTH(Report!$L$38)+1,0)),MATCH(chtMonth,Report!$B$2:$J$2,0))
chtMonth      =TEXT(Report!$L$38,"mmm")

Then I assigned the range cht1Labels to the Categories (X Axis) and the cht1Data to the values. Note that the X axis is dynamic with regards to number of days shown per month.

The second chart could be set up in a similar way, but since it has so many series, the helper columns as suggested by Dave will be quicker to set up.

cheers, teylyn
Chart.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36526936
Good one.  I was trying to ensure it could be maintained a bit more easily, so opted out from named ranges on this go...

Dave
0
Independent Software Vendors: 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!

 

Author Comment

by:Cartillo
ID: 36526990
Hi teylyn,

Hope you will consider to make chart 2 works similar as chart 1.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 36527073
Cartillo, as I said, it can be done, but Chart2 has 25 individual series. With two range names per series, one for X Category and one for data, you'd need to define and apply 50 range names. The solution posted by Dave is a lot quicker.

On the other hand, you could change Chart 2 by clicking "Switch Row/Column". The chart has now one series only and they are labelled by the X axis. Format the series to "Vary colors by point" on the Fill tab. Reduce the gap width a bit to make the columns fatter.

Then define a range name

cht2Data =OFFSET(Report!$M$3:$M$27,,MATCH(Report!$L$38,Report!$N$2:$R$2,0))

and apply it to the chart series.

Even better would be a horizontal bar chart, so the category labels have the same orientation as the chart bars AND are easy to read. I put such a chart below the other two.

See attached.

cheers, teylyn
Chart.xls
0
 

Author Closing Comment

by:Cartillo
ID: 36528504
Hi Teylyn/Dave,

Thanks a lot for creating this dynamic charts for me.
0

Featured Post

Independent Software Vendors: 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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

765 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