Referencing Data listed Vertically into Horizontal Table

[SeventFloorProfessor]

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

 

[Luke M]

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.

 

[Sean Timmons]

=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]

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!

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

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

 

[Sean Timmons]

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)

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!

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

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

 

[SeventFloorProfessor]

Okay, I will play with it some... a couple of questions...

Does it matter if my "array" is an excel file, and not a data file?
What are the "--"?

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)

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!

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

:

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

 

[SeventFloorProfessor]

Also, I finally wised up and copied and pasted the information from file
"Test Data" into file "Assessment Results" as a new sheet, "Data". I don't
know if this changes anything...

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)

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!

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

:

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