|First, a posting tip:|
Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum. Those instructions will show you how to line up your data in columns when you post.
As for your question, I think the VLOOKUP function would work for you. Read the Help files on VLOOKUP since I'm sure you'll need to modify these instructions to fit your exact needs. This just a concept, so feel free to just use the ideas to get you thinking.
Let's say you start with this in Sheet1:
A B C D etc.
1 Student Week 1 Week 2 Week 3 etc.
Enter Week 1 in Sheet2!A1
Paste your Week 1 data into Sheet2!A2 so that you have this:
1 Week 1
2 anna 45%
3 paul 35%
4 chris 12%
In Sheet1!B2 enter this formula:
=IF(Sheet2!$A$1 = B$1,VLOOKUP($A2,Sheet2!$A:$B,2,0),"")
Drag it down to the bottom of your list of names and over to Week 52. This should fill in the values for each student for Week 1 and leave empty cells in all the other weeks.
Now do an Edit...Copy...PasteSpecial - Values on Sheet1 Column B to eliminate the formulas and leave the actual values.
Next week (Week 2) paste your new data over the old data starting in Sheet2!A2 and change Sheet2!A1 to read Week 2. This should fill in the Week 2 data on Sheet1 since the Sheet2!A1 now matches Sheet1!C1.
Now do an Edit...Copy...PasteSpecial - Values on Sheet1 Column C to eliminate the formulas and leave the actual values.
Give it a try and let me know what you think.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.