Automatically transfer data from several sheets to a master sheet

Hello all,

I am wondering if anyone can help a perplexed grad student figure out how to enter data more efficiently. My problem is I want to enter data on multiple spreadsheets within the same book, and that data to automatically show up on one master sheet. I don't know how to explain exactly what I'm doing so I will use an example. Let's say I am a teacher and I have 5 classes and I am recording the test scores for each student. Each class is a separate spreadsheet (sheet1-5) and I have one final spreadsheet (Sheet6) that I use as a master sheet with all the students listed that I use to graph their progress. Is it possible to enter date on sheets 1-5 and it automatically show up on on a list on the master sheet? Furthermore, can I get it to show up on the next available row for each student? For example,every time I enter a new score for a student on sheets 1-5, the new score will show up on the box to the right of the old score so all the old data plus the latest new score is visible on the master sheet.

I really hope this makes sense and someone out there can help me out. It would be greatly appreciated!

Hi and welcome to the forum

Im sure we can put something together for you on this. Have you started putting anything together yet? Like, what will the "class" sheets look like and what will the "master" look like?

Take a look at the attached, I changed things a little, to help with the flow. If you prefer it the way you had it, we can work around that.

This formula all revolves around each column on each sheet being the same as the same (relative) column on the other sheets...sheet1 column D=sheet2 column D=sheet3 column D. If you have headings, we could use those instead

The basic formula is...
INDEX(INDIRECT("'"&$A2&"'!$A$1:$H$6"),MATCH('sums to 9'!$B2,INDIRECT("'"&$A2&"'!$A$1:$A$6"),0),COLUMN()-1)
broken down, this is an index/match combo...
=INDEX(sheet1$A$1:$H$6,MATCH('sums to 9'!$B2,sheet1!$A$1:$A$6,0),COLUMN()-1)
=INDEX(range,row-num,col-num)

Because you are referencing different worksheets as you progress down the table, I used INDIRECT() to reference and use that data from column A. INDIRECT() is a function that can take regular text (like a sheet name in a cell), and convert it to something that excel can use in a formula
so...
INDIRECT("'"&$A2&"'!$A$1:$H$6")
says...use the contents if A2 (as the sheet name), and combine that with the range $A$1:$H$6, to give me a sheet name range reference. The "'" and "'! need to be there to keep with sheet referencing rules - in class1, reference A1 on sums to 9 sheet, and you will see the context ='sums to 9'!A1. You will need to adjust the A1:A6 manually in that formula to suite your range, I could biold it in, but I wanted to keep it "relatively" simple

With that said, if you include Class4, class5 etc sheets, and add them to the summary, just copy the formula down and across and you should be find

You may have noticed that your chart now takes a dive where there is no data. There are a few ways around this, probably the simplest might be to hide the columns that dont (yet) have data, or delete the formulas in those cells/columns