Instead of using labels "Question #1", use a label of just "1" (although you
could insert a row above saying question, for aesthetics. Also, have both
files opened when you do this, will hlep Excel find the correct file you are
referring to.
Assuming first question score goes into C2.
=SUMPRODUCT(('[Test Data.xls]Sheet1'!$A$2:$A$250=$A2)*('[Test
Data.xls]Sheet1'!$B$2:$B$250=B$1)*('[Test Data.xls]Sheet1'!$C$2:$C$250))

Of course, change Sheet1 to whatever you have the sheet named in your Test
Data file. You should then be able to copy this down and across as needed.


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SeventFloorProfessor" wrote:

First, I have no idea how I would search for this question, so if it has been
asked and someone would point me to it, I'll look there. Here is my problem:

I have an Excel File "Test Data". I have another Excel File "Assessment
Results".

The "Test Data" file is imported from a testing program, which automatically
scores multiple choice tests. It lists the result of a each student's answer
to each question in vertical rows, like this:

Name Question Points Earned
Student, A 1 1
Student, A 2 0


And so on for each student.

My "Assessment Results" file is structured like this:

Name Question #1 Question #2
Student, A 1 0
Student, B 0 1

So, I would like to be able to copy and paste the formula and have it
correctly move down the vertical list in "Test Data" when it autoupdates the
formula, rather than have to show Excel where to find the correct cell in
"Test Data" for every single student & question (that's 17 questions x 14
students for one class, alone).

I hope this makes sense...