Create a chart from data on Multiple pages

[therempels]

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):
[TABLE="width: 2303"]
<tbody>[TR]
[TD][TABLE="width: 1070"]
<tbody>[TR]
[TD]Student Number[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Birthdate dd-mmm-yy[/TD]
[TD] Grade [/TD]
[TD]Age at Test Term[/TD]
[TD]CAT Level Tested[/TD]
[TD]Expected CAT Level[/TD]
[TD]Actual Grade at Test[/TD]
[TD]Grade Equivalent Difference[/TD]
[TD]Overall Grade Equivalent[/TD]
[TD]Math Grade Equivalent[/TD]
[TD]Reading Grade Equivalent[/TD]
[TD]Writing Grade Equivalent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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

 

[AlphaFrog]

Have you looked into creating a Pivot Table and Pivot Chart from your data on multiple sheets?

Excel Pivot Table Tutorial -- Multiple Consolidation Ranges

There are several other good tutorials on the web.

 

[therempels]

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