Gradebook and Individual Report

[Robin]

Hi all,

I am a teacher and preparing a class marksheet (gradebook).
I have completed the marksheet with 24 columns (16 subjects plus 8 for other
info like total, percentage etc) and 50 rows for students' names.
Now I would like to transfer each students record into an individual report
card on another sheet.

Can this be done?
I have prepared the format for the individual report.
But the marks from the marksheet just couldn't go to the correct cells in
this individual report card.

Please help me.
Is it possible if I send you a sample of my gradebook for you to modify.
Thanks.

Regards,
Rudy

 

[Debra Dalgleish]

It may be easiest to print the individual reports by using Microsoft
Word's Mail Merge feature to pull the data from Excel.

There's information on the Word MVPs' web site:

http://www.mvps.org/word/FAQs/MailMerge/

 

[Max]

Suppose that in Sheet1 (your marksheet),
you have the table structure below (topleft cell in A1)
with labels in row1, data in row2 down, viz.:

Name Sub1 Sub2 Sub3 Sub4 Sub5 etc >>
ABC
DEF
XYZ
etc

In Sheet2 (a typical individual student sheet)
let's say the report format is as below:

In A1: enter Student's name, e.g.: ABC

In A2 downwards:

Sub1
Sub2
Sub3
Sub4
Sub5
etc

Put in B2:
=OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!A:A,0)-1,ROW()-1)

Copy B2 down as many rows as you have Subjects or fields in col A

Col B will return in a "column-wise" format
the necessary data from Sheet1 for Sub1, Sub2, etc
for the student name specified in A1
----------------

You can also copy the above Sheet2 construct
for the remaining 49 students at one go
by using sheet grouping
(assuming all 49 new sheets are already inserted:
viz. Sheet3 - Sheet51)

In Sheet2
Press Ctrl+A to select the entire sheet
Click Copy

Click on Sheet3, hold down Shift key
then scroll to & click on Sheet51
(assume Sheet3 and Sheet 51
are the extreme "book-ends")

Right-click on A1 (in Sheet3) > Paste

To ungroup sheets
----------------------
Right-click on any of the grouped sheets tab > Ungroup Sheets
or just click on say, Sheet2

Now you just need to enter
the students' names into each of the 49 sheets

 

[Don Guillett]

a for each macro should do it

for each cel in range("a2:a200")
[sheet2!a2]=cel.offset(1)
etc
sheets("sheet2").printout
next

You may send me the workbook (if not too large) and I will have a look.