• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Dynamic Chart based on Month

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
Cartillo
Asked:
Cartillo
  • 2
  • 2
  • 2
2 Solutions
 
dlmilleCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
dlmilleCommented:
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
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.

 
CartilloAuthor Commented:
Hi teylyn,

Hope you will consider to make chart 2 works similar as chart 1.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
CartilloAuthor Commented:
Hi Teylyn/Dave,

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

Featured Post

Industry Leaders: 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!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now