Dynamic chart - dropdown

[3G]

Hi -

I am trying to make a dynamic pie chart, where the user can select up to 2 options (Department, Year). I'd like the pie chart to update automatically based on the selections. I've noticed Chandoo has a few dynamic chart tutorials (check box/etc), but, no drop down driven ones.


On his Dynamic Dashboard illustration, he has put the data on the same page as the chart, but, I have mine on a different sheet. Additionally, he has some SUMIFs running in his data matrix to calculate the cells based on the selections of the dropdowns, but, my calcs are done...I just want the data updated. I'm sure it's an OFFSET formula, but, I'm struggling to get it down. Any input?


Thanks!

3G

 

[Luke M]

Two ways to do this:

a) Inputs change formulas, which feed the chart data

b) Inputs change dynamic formula, thus changing what the chart source data is


For a:

Create 2 dropdowns from the forms menu, and have them linked to a cell somewhere. You will then use these inputs to drive the formulas creating the chart source data (what that looks like depends A LOT on what your data layout looks like)


For b:

Same as a at first, up to linking the dropdowns to cells. Then create a named range, with a dyanmic formula (most likely an OFFSET function) that is driven by 2 parameters which vary the Row and Column offsets (parameters 2 and 3 in the function). These parameters need to come from the linked cells. This formula should define what the chart source data is. Then, in your chart, define the series value formula to reference the dynamic formula.


This isn't a complete tutorial of how to make the chart, as I'm unsure what your layout is, and/or how much of Chandoo's tutorial(s) you've understood. Does this help?

 

[3G]

Hi Luke-

Thanks! I think option B is what I want. I'll give it a shot and check back.

 

[vasim]

Use this...


http://chandoo.org/wp/2011/03/16/analytical-charts-tutorial/


it should be helpful

 

[3G]

Ok! I got it to work, but, now I'd like to place the chart & dropdowns on a different page than the data. However, the data validation won't let me pick the list from the other page. Does this mean I need to create named ranges?

 

[Hui]

3G

Put your formula, equation or range from the validation into a Named Range called list

Put =List into the Validation

 

[3G]

Great! Thanks Hui. Got it to work.


Now, I'm having a problem with the pie chart going in the "other direction".

I can only get the pie to update by picking the year from the drop down. My formula is as follows:


=OFFSET(INDEX($C$5:$F$22,MATCH($H$2,$B$5:$B$22,0),MATCH($H$3,$C$4:$F$4,0)),0,0,14,1)


Where B is my list of Departments, and, C4:F4 is my years (Total, 2011, 2012, 2013). How can I get it so that when they pick the COMBO of department & Year, it will display the data for department 1 only across the three years (2011, 2012, 2013)? Then, I'd like the option to pick all departments too. Again, the year option is picking all of them just fine.


Thoughts?

 

[Luke M]

To do all the apartments, you'll need a row that is the sum of all the other rows. I'll assume you have it at the bottom (row 22?)


First though, let's simplify the formula a bit:

=OFFSET($B$4,MATCH($H$2,$B$5:$B$22,0),MATCH($H$3,$C$4:$F$4,0),14,1)


There. Now it's a bit easier to see that the 1st MATCH function is controlling which row (department choice) and the 2nd MATCH controls year. However, I'm a bit confused by your last 2 arguement callouts of 14 and 1. Why do you need 14 rows of data? Since you want 3 years of info, I would have thought the formula would be:

=OFFSET($B$4,MATCH($H$2,$B$5:$B$22,0),MATCH($H$3,$C$4:$F$4,0),1,3)


This says that you want 1 row of data (either the dept or the row that sums all departments) and 3 years.

 

[3G]

Thanks Luke! Super helpful.


Maybe I didn't state it right from the start...I have 14 rows of data x 4 columns. 14 rows = 13 departments + 1 "ALL", the 4 columns = Total, 2011, 2012, 2013.


I would like the option so that someone could pick either ALL of the departments (All Departments" would be a choice), and look at The Total, 2011, 2012, or 2013, OR one of the depertments, and look at the Total, 2011, 2012, & 2013.


The data lives on another page than the drop downs & where I'd like the chart to go. This is a pie chart, so, when they select the "ALL", I want the pie to show all 13 in it.


Sorry if I was unclear from the start.

 

[Luke M]

Ah! So, the departments would be the different pie slices. The problem with the current setup is that your first view requires the chart to look at each row as a data point, while the latter requires looking at each column. This is a toggle within the chart options, not just a source data. The more I think about this, the more confusing the visualization of making it works.


I'd strongly recommend using a line or bar chart. It would make it easier to compare between years and pie charts are notoriously bad at being hard to accurately read. I recommend taking a few moments to check out Jon Peltier's latest post about pie charts. He shows several alternative; perhaps one of them would suit your purpose better?

http://peltiertech.com/WordPress/chart-busters-pie-charts-cant-show-trendlines/