No problems! :-)

the formula from below still appies. Just need to change the cell references
to match.

=SUMPRODUCT(--('[Test Data]TestScores'!$A$1:$A$5000=$B4),--('[Test
Data]TestScores'!$B$1:$B$5000=B$1),'[Test Data]Test Scores'!$C$1:$C$5000)

"SeventFloorProfessor" wrote:

I don't think I was very clear, because I provided too much information. You
may still be correct in what I should do.

File 1: "Test Data" Sheet: "Test Scores"
File 2: "Assessment Results" Sheet: "Enter Results"

The "Test Data" file has a chart with the student's name and whether or not
they earned a point for each of 17 questions, like this (I included the "Row"
here to help; it's not actually a column in my file. The number in () after
each column heading denotes the column it is in.):

Row Name (A) Point Earned (C)
2 Student, A 1
3 Student, A 1
<skip to next student
19 Student, B 1
20 Student, B 0
<skip to next student
36 Student, C 1
37 Student, C 1

<and so on...

The file "Assessment Results" that I use to assess the data is all set up
with student names, the formulas I need to analyze scores, etc. It looks like
this (I included the "Row" and "Column" here to help; it's not actually a
column in my file):

Row Name (B) 1(C) 2(D) 3(E) <to 17(V)
4 Student, A 1 1 0
5 Student, B 1 0 0
6 Student, C 1 1 1

I would like to enter a formula in "Assessment Result" C4 that copies the
value in "Test Data" C2. Then, when I copy it, "Assessment Result" D4 would
have the value from "Test Data" C3, and so on, until "Student, A" scores are
taken from "Test Data" C2 to C18.

The problem is that "Test Data" lists the values vertically, and "Assessment
Results" lists them horizontally, so Excel won't autoupdate the way I'd like
it to! Also, I don't know how to tell it to autoupdate so that the value for
"Student, B" on "Assessment Results" C5 to V5 begins on "Test Data" C19 and
continues to C35 (again, because "Test Data" lists the seventeen values
vertically).

I know this is confusing. I'm just trying to spend less time grading and
entering data, and more time analyzing data.

Thanks!

"Sean Timmons" wrote:

=SUMPRODUCT(--('[Test Data]Sheet1!$A1:$A5000=$A2),--('[Test
Data]Sheet1!$B1:$B5000=B$1),'[Test Data]Sheet1!$C1:$C5000)

Will work assuming test data is the name of the other file, and info is in a
sheet named Sheet1.

also assumes rep names arei n column A and other info in columns B and right.

This formula goes in B2 and can be copied and pasted to the bottom and right.

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