[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Dynamic Chart based on Month

Posted on 2011-09-12
6
Medium Priority
?
336 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

650 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