How to create one report card instead of 35?

Hi everyone

New here. Need some help. Although I have a basic knowledge of formulas and referencing, I am stumped with this current problem.
Background info-I am a teacher and have set up a marks programme where the teachers type in their marks on different sheets (ie per subject) and these all get linked to a mark schedule which has a summary of the codes and perecentage per subject per term for each learner. Very easy to do with copy + paste link. But tedious... From the mark schedule the marks a linked to reports for the learners.

Now here is my question: At the moment I have 35 sheets in a workbook assigned to reports, each one per learner. Is there no way just to have one report template and therefore one sheet which displays which ever child I wish to see at that specific time?

On the mark schedule each learner is assigned 4 rows for marks, one per term. Columns are assigned per subject. 2 columns per subject, one for there code ( code being 1-4 representing a range of percentages eg. code 1 is 0-35%, code 2 36-49%, etc.) and the other for the actual percentage.

Is there any way to just have one report card and then say I select child 20, it displays that learner's marks on the report card?

Many thanks from a self confessed dummy

Yes its very much possible. Why dont you attach a sample workbook, showing what you currently have and also what you would like to see?

To Attach a File:

1. Click on Go Advanced.
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Report Card.xlsxMark schedule.xlsx

These are my mark schedule and report card. There are 35 report cards in a workbook. The marks are linked from the mark schedule to each report card. As you can see, each child has 4 rows that will contain marks. Each row is that terms marks. I want to know how I can have one report card instead of 35.

I wouldn't even know where to start!

Many thanks

Can you explain for one person where the data in the report card comes from?

It will be good if you explain with the help of some cell references, like cells A2:C2 should be added and averaged and the value entered in the report card cell A2, etc. This will help us understand how to proceed with the report.

No, what i mean is why dont you provide a few cell references so we understand what data goes where. You understand your report 100% but we dont. So to make it easier for us to help you, just list out a few of the cells.

To achieve this you would be best to have everything in one workbook.

One sheet would contain a table with all the results in for each student listed in column A. There should be no empty rows or columns in the data.

Another sheet can be set up as the report with VLOOKUP populating it from the table of data based on Student names