Dynamic Pie Graph - based on two dynamic fields


New Member
May 25, 2016
Any help is much appreciated. I apologize if it's hard to follow. I initially wrote it up in an excel attachment, but it looks like I can't attach workbooks to the thread. :( Please let me know if I can clarify.

Thank you!

Data Collection: Three separate teachers each surveyed their 12 students on how comfortable they felt with a particular activity. One teacher used 3 categories that were different than Teachers A & B, but the same rating scale was used.

Data Analysis: The teachers now would like to see how their students rated their familiarity with each activity in a chart.

Data Presentation: They'd like to be able to see a pie chart that shows each activity separately for only their class. They would like to be able to have a drop down selecting which activity. They only want to see the "buckets" (aka activities) that they surveyed.

Data Process: I think creating a dynamic pie chart where each teacher can select their name from a drop down, which would feed to another drop down with only their list of questions, would be the easiest for the teachers to use.

Questions: In order to create a dynamic graph, I've read that I need to have it linked to separate (duplicative) chart with just the data that is being displayed. I'm not sure how to do that with two dynamic variables. If that's not needed, I'm open to other options!

1) Dependent Drop Downs: I can set up a single data validation drop down (list) with the teachers names. How can I create a second drop down that can bring back only the questions they were asked? I can create two separate lists, one for teachers A&B, and one for teacher C. However, how do I make that drop down contingent upon what is selected in the first drop down?

2) Multiple Vlookup with Dynamic Values: I can easily create a vlookup formula that looks up the data for Teacher B, but can't figure out how to also only pull back the "1" ratings for that cell, the "2" ratings, etc. I can't do a hidden concatonated cell (to my knowledge), as the fields are dynamic.

3) Only Columns Surveyed Visible: If the chart has all the columns for all teachers combined, that's fine. However, how can I make the graph only show the ones surveyed?


Goal- When Teacher B selects "Teacher B", and "Bike Ride" from these drop downs, the pie chart is displayed.

Teacher B(drop down) Students Surveyed
Bike RideRollerskateIceskateSkateboardRun

**Ideally, these charts would be have a formula that said… Count the number of instances where within the raw data sheet, Teacher B had a a student rate Bike Riding a 1, etc. Only activities surveyed would be presented. The activity and the teacher would be dynamic fields.

Teacher: (Drop down) Activity: (Drop Down)

Pie Chart

Title: _____(Activity Selected)______

(Pie Chart)...

<colgroup><col span="6"></colgroup><tbody>
Raw Existing Data-
TeacherRatingBike RideRollerskateIceskateSkateboardRunReadWriteSleep
Teacher A115044
Teacher A205544
Teacher A350441
Teacher A462303
Teacher B141051
Teacher B2121145
Teacher B345103
Teacher B434033
Teacher C14600
Teacher C2610
Teacher C3051
Teacher C4 0611

<colgroup><col span="6"><col span="3"><col></colgroup><tbody>

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back