Solved

Dynamic Chart based on Month

Posted on 2011-09-12
6
328 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 250 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

Expert Comment

by:Ingeborg Hawighorst
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 41

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 earned 250 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

896 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

17 Experts available now in Live!

Get 1:1 Help Now