Sheets Commands

[madmardjealas]

Hello from a first timer on this forum. My boss built a report card which is so stiff and complicated I usually have to enter data from 4 or 5 different spreadsheets onto one. I've been introduced to some neat Excel commands, but have yet to figure this one out.

First, I have an excel sheet1 with this data on it:

Course	Mark	Out Of
A Day in the life of a Medic		10.0
EMS & City SOP's		10.0
Patients with a Tracheostomy		10.0
NRP		10.0

<tbody>
</tbody>

Now the students are working on a tablet that generates an excel spreadsheet of marks in this format:

Name	A Day in the life of a Medic	EMS & City SOP's	Patients with a Tracheostomy	NRP
Bob Smith	10	7	9	10

<tbody>
</tbody>

These marks are on sheet 2. I want to get these marks to sheet 1, base on the students name and the course name. Any ideas would be greatly appreciated. Otherwise I have to enter each mark individually, about 8500 data points. yay.

 

[bbott]

Welcome to the board.

Where does the student's name appear on Sheet1?

Or does each student have their own workbook?

 

[madmardjealas]

THanks for thereply. So what I have is an excel report card file with a bunch of students on it, one eachper sheet. It looks like this:<o></o>

	Personal Information<o></o>
Name<o></o>	0<o></o>
Email<o></o>	0<o></o>
Address<o></o>	0<o></o>
Phone<o></o>	0<o></o>
Employee #<o></o>	0<o></o>
Course<o></o>	Mark<o></o>	Out Of<o></o>	Comments<o></o>
A Day in the life of a Medic<o></o>		10.0<o></o>
EMS & City SOP's<o></o>		10.0<o></o>
Patients with a Tracheostomy<o></o>		10.0<o></o>
NRP<o></o>		10.0<o></o>

<tbody> </tbody>

The Students do their written tests on a tablet that generates another excelspreadsheet that looks like this:<o></o>

Surname<o></o>	Quiz: A Day in the life of a Paramedic<o></o>	Quiz: EMS & City SOP's<o></o>	Quiz: Patients with a Tracheostomy<o></o>	Quiz: NRP<o></o>
Alfonsi<o></o>	9<o></o>​	-<o></o>	-<o></o>	-<o></o>
Brennan<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Burnett<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Cann<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Carkner<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Chung<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Cobus<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Couture<o></o>	9<o></o>​	-<o></o>	-<o></o>	-<o></o>
David<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Delany<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Desson<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Deveros<o></o>	9<o></o>​	-<o></o>	-<o></o>	-<o></o>
Di Paolo<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>
Drew<o></o>	10<o></o>​	-<o></o>	-<o></o>	-<o></o>

<tbody> </tbody>

What I want to do is copy and paste it onto the same excel report card file, say, at the end on the last sheet, which I will name"grades" or something similar. What I would like to have happen isfor the marks to post from the sheet I copy them to, to the individual studentreport cards, which are each on a separate sheet but all on the same excel file.Maybe I am asking too much but I'd like to know if its possible. Thank you.<o></o>

 

[bbott]

Let's assume that:

1. The grades are on a sheet named Grades.
2. The student's name is in C2 on the grade card sheet.
3. The names of the quizzes are the same on each sheet. In your example, the first exam is called A Day in the Life of a Paramedic on one sheet but A Day in the Life of a Medic on another. These will need to match for this formula to work. It is OK that they start with the word Quiz on the grade sheet.

Put this formula in B12 on the grade card sheet and copy down:

=INDEX(Grades!$B$2:$E$15,MATCH($C$2,Grades!$A$2:$A$15,0),MATCH("*"&A12,Grades!$B$1:$E$1,0))

You may need to adjust the ranges for your actual data.

 

[madmardjealas]

Let's assume that:

1. The grades are on a sheet named Grades.
2. The student's name is in C2 on the grade card sheet.
3. The names of the quizzes are the same on each sheet. In your example, the first exam is called A Day in the Life of a Paramedic on one sheet but A Day in the Life of a Medic on another. These will need to match for this formula to work. It is OK that they start with the word Quiz on the grade sheet.

Put this formula in B12 on the grade card sheet and copy down:

=INDEX(Grades!$B$2:$E$15,MATCH($C$2,Grades!$A$2:$A$15,0),MATCH("*"&A12,Grades!$B$1:$E$1,0))

You may need to adjust the ranges for your actual data.

Thank you for the reply. I will start working on this and get back to you.

 

[madmardjealas]

Hi again, I wasn't able to make the formula work. I will add a couple of screenshots to better illustrate the problem.

below is a shot of the report card sheet:

[/URL][/IMG]

This image is the report card generated by the tablet, and is a sheet in the same file named Grades.

[/URL][/IMG]

All the same conditions apply, at least now you can see the fields. Thanks for your time. Regards, Marc

 

[bbott]

Try:

=INDEX(Grades!$F$2:$J$15,MATCH($D$2,Grades!$A$2:$A$15,0),MATCH("*"&A12,Grades!$F$1:$J$1,0))

 

[madmardjealas]

Thanks bbot for the suggestions. I wasn't able to make yours work, so I ended up using this:=TRANSPOSE(Grades!G4:AX4) in the report card. I highlighted the fields next to the tests on Alfonso, did my =transpose(grades!b2:ax2) then shift-ctrl-enter, then copied it to the other cells and it seems to work fine. Your suggestions at least put me on the right path, and I appreciate your help.

Regards,
Marc