Create a chart from data on Multiple pages


New Member
Jan 14, 2012
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


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
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!

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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! :)
Upvote 0

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
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 "".
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