Changing charts based on cells

ShoYnn

New Member
Joined
Mar 20, 2019
Messages
6
This probably sounds familiar, but I have been rifling through google and herd all day to find a solution. Here is some background information:

The spreadsheet is for trend analysis on tests. You put in the class number, number of students, and what version of the test they are on(there are 3 versions, A is what they all take, B is what they take if they fail A, etc...). Next you put in what the students put as answers(multiple choice A-D) for each question for each student. When finished you click a submit button that flushes the information to start filling in a data array. The sheet calculates avg class score and what percent of the class got the wrong answer for each question.

Now onto the fun stuff. I am eventually going to have 2 separate charts, one to trend class average, and one to trend the wrong answers. For now let's focus on the class avg as this will be simple.

What I want to do is have the graph be able to show me data for individual classes or every class that has been entered based on two drop down boxes, one selecting the version of the test and one selecting either a specific class or all classes. I already have named dynamic ranges for those scenarios, I just need a way to make the spreadsheet change what named ranges the chart is using based on those drop boxes. Formulas or VBA are fair game. Thanks!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows
Hi, you can create a drop-down list that has for example the class name/individual name, then you can create table that has the data of the classes and simply use formula SUMIFS to bring all the data of each column/ row you have.
If you could share a sample table with names of the columns I can share the formula with you.
 

ShoYnn

New Member
Joined
Mar 20, 2019
Messages
6
Sorry I didn't reply earlier, I figured out a few things I was doing wrong, realized that one of my graphs actually needed to be split into 10, and by time I did all of that I was totally in the zone. I eventually just moved the graphs to the sheet that I waz flushing the data to and that solved most of the issues (no neex to try and call the data back to the main sheet...), and I even figured out a way to automatically adjust the wixth of the graph so that when there is a crazy amount of data on it it is not all squished together. This is what I used to accomplish the changing ranges of data on the graph, and it worked really great!

https://youtu.be/sHfWRb2yUrM

Thanks for the initial response though!
 

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows
Happy you figured this out, and thank you for sharing this great video.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,970
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top