with excel had a report writer Closed

Question

Hello,
I have a spreadsheet with student names, each student is assigned to one of 4 teachers. Student name in A, teacher name in B other data in the rest of columns.  I would really like to be able to create something that looks like a report with each teacher name at the top of a column and the students listed below, then count the students.  I can do this with a seperate sheet, but the lists are changed frequently, so if I link the initial sheet to the second sheet (that looks like the report) so I don't have to remember to change both sheets - when the first list is changed and resorted - the report sheet all the names move and are no longer under the correct teacher.  If  Doe, Jon is in cell A6 if I add Carr, Bob;  Doe, Jon becomes A7 and all other names move down in cell point.  When the cells are linked to the second sheet the names move.  

I made the teacher name in A and student name in B then tried the vlookupm but since the teacher name appears multiple times this would only return the last student name for each teacher.  

I have excel 2003.  Any suggestions?

kaiyasit · Answer

Please try to use the "Pivote Table"
This can help you 100%.

Rather be Knitting · Answer

I tried the pivot table, but I would like the 4 groups to appear horizontally and the names to be vertically under the teacher name headings:
Teacher name                Teacher name             Teacher name            Teacher name
student                              student                        student                     student
student                              student                        student                     student

You get the idea.  The teacher names don't change, but the students get moved around among the teachers.  my source list is all the student in alphaetical order in the first column and the teachers are listed in the next column, so when I paste link to set a sheet to look like the above the names all move - not  just the ones I add or delete from under the teacher I changed.  Is there a formula I could use that says: if column b="teacher x" then return the student name from column A that is in the same row?

I am trying to avoid having to change the same data on multiple changes (prime opportunity for errors)

kaiyasit · Answer

Fot the "PIVOTE TABLE" using,
You should collection the data by

Column A(Student) , Column B(Teacher)
and on each row data must be have one by one for the Student name vs Teacher name
like as

Cell A2 = StudentName A , Cell B2 =  Teacher name X
Cell A3 = StudentName A , Cell B3 =  Teacher name Y
Cell A4 = StudentName B , Cell B4 =  Teacher name X
Cell A5 = StudentName C , Cell B5 =  Teacher name X
Cell A6 = StudentName D , Cell B6 =  Teacher name Y

the pivote table independent on row data collection
Please try.....

sent sample file to me, i will make sample and sent back to you.....

Best regards,
Kaiyasit Phanmakorn
 kaiyasitp@gmail.com

With excel had a report writer

3 responses

Similar discussions

Subscribe To Our Newsletter!