Create a chart from data on Multiple pages

therempels

New Member
Joined
Jan 14, 2012
Messages
4
I need to create a chart from data on multiple pages of a worksheet.
This is for a long-term summary of standardized test results for a school. The workbook is set up with all the results for a particular year on each sheet, with at least 12 sheets for past years. Students will not necessarily be on the same line on each sheet, as there are additions & deletions to the school population over the years.
These are the input fields at the top of each worksheet (and are the same on each sheet):
Student NumberFirst NameLast NameBirthdate dd-mmm-yy Grade Age at Test TermCAT Level TestedExpected CAT LevelActual Grade at TestGrade Equivalent DifferenceOverall Grade EquivalentMath Grade EquivalentReading Grade EquivalentWriting Grade Equivalent

<tbody>
</tbody>

<tbody>
</tbody>
I want to create a dynamic chart for each student (not for each grade) that will have "Grade Equivalent" on the y-axis, and a combination of "Year", "CAT Level" and "Grade" on the x-axis
Like this:
Student Name
5
4
3
2
1
2012 2011 2010 2009 etc...
14 13 12 11
Gr. 4 3 2 1
Where the values of "Overall Grade equivalent", "Math G.E.", "Reading G.E.", "Writing G.E." are columns for each of the points on the x-axis.

The chart could be a pop-up, or could have its own page. I will need to be able to print each chart, also.

Can I get excel to create a chart based on the unique student number as it finds the number across any of the multiple pages?
And if there is a way to do this :), can I also have it automatically update the chart as new sheets are added to the workbook (ie. for 2013, etc)?

(I still learning excel, but I am a quick study! I have designed other workbooks with less-complicated summary charts. I have also had success with conditional formatting and formulas)
I have done a lot of searching, but haven't been able to find anything that fits my criteria.
Any help would be appreciated. :)
Thanks in advance for any help!
KR
 

therempels

New Member
Joined
Jan 14, 2012
Messages
4
I was hoping (rather naively, I'm afraid) that I would be able to define ranges to make my charts without having to rearrange all my data. I have spent today trying many different ways to get what I want, and the only way I can do it (so far) is to copy and paste the values from all my sheets into a new sheet so I can work with them.
What I really want is a dynamic file that will automatically create a chart for each unique Student ID number (there are about 200), including adding to the chart when a new set of data is added with the student's ID number to another sheet (ie, in 2013 when there is a new set of test scores). Each student ID could have up to 13 years of data (if they are in grade 12) for the x-axis, and needs 4 columns per year to display the different areas of each test.
I am using Excel 2007 PC and even trying Excel 2011 Mac.
:) I'm sure all this is probably just outside the realm of 'impossible', but any suggestions that could get me closer to my goal would be appreciated.
1) I would like a function that will consolidate data from all sheets to one sheet. This would give multiple entries for each student's ID that vary by YEAR and SCORE. Excel's consolidate only seems to sum, etc, but not simply put all data together. From here, I can sort by ID, and then create the chart for scores by year for each ID. This is what I have now done by 'cut and paste' in my sample file, but I would like a more automatic way to do this. I have named ranges, formulas and conditional formatting in the main sheets, but I only need the values pasted in the summary sheet.
2) I would like to somehow automatically create charts for each unique ID without having to do it manually. With my sample data, I have to create the chart, and name the axis, etc for each. This is way too time-consuming for 200 charts. Is there a way to do this more easily? I saved a chart template, but it doesn't hold the axis names, titles, etc)
3) Also (off main topic, but still part of my dilemma) Can I have more than one row of data on the x-axis without creating a new chart? I want to reference my existing data and show the grade and age under the year (It is already in adjacent columns in my data)
Thanks for any suggestions! :)
KR
 

Forum statistics

Threads
1,081,728
Messages
5,360,923
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top