Dynamic Pie Graph - based on two dynamic fields

[KristiMiller]

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?

Examples)

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


[TABLE="width: 504"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]Teacher B(drop down)[/TD]
[TD] [/TD]
[TD="colspan: 3"]Students Surveyed[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Bike Ride[/TD]
[TD]Rollerskate[/TD]
[TD]Iceskate[/TD]
[TD]Skateboard[/TD]
[TD]Run[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]3

[/TD]
[/TR]
[TR]
[TD="colspan: 6"]**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.

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


Pie Chart

Title: _____(Activity Selected)______

(Pie Chart)...



[/TD]
[/TR]
</tbody>[/TABLE]
Raw Existing Data-
[TABLE="width: 776"]
<colgroup><col span="6"><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Teacher[/TD]
[TD]Rating[/TD]
[TD]Bike Ride[/TD]
[TD]Rollerskate[/TD]
[TD]Iceskate[/TD]
[TD]Skateboard[/TD]
[TD]Run[/TD]
[TD]Read[/TD]
[TD]Write[/TD]
[TD]Sleep[/TD]
[/TR]
[TR]
[TD]Teacher A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Teacher A[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Teacher A[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Teacher A[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Teacher B[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Teacher B[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]11[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Teacher B[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Teacher B[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Teacher C[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Teacher C[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Teacher C[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Teacher C[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]