Solved

Dynamic Chart based on Month

Posted on 2011-09-12
6
327 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:teylyn
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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:
teylyn 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 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

12 Experts available now in Live!

Get 1:1 Help Now