print column labels with one row of data at a time.

[Guest]

I am a teacher. I have created my gradebook in excel. (I don't have
access). I have 7 worksheets (one for each class that I teach). The
worksheets are not exactly the same.

I would like to be able to print out the top two rows (column labels) with
each students data (row). This way the student can see row 1 (name of
assignment), row 2 (points), their row (their personal data for each
assignment).

Any help??

 

[Guest]

Go to
File > Page Setup > Sheet

Then in the "Rows to repeat at top" highlight the label headings row(s).

HTH

 

[Barb Reinhardt]

You may want to write a macro to do this. I'm new to VB so am
cross-posting this in microsoft.public.excel.programming.

 

[Barb Reinhardt]

Naz,

I think the question is how do you print one page for row 2, another for Row
3, another for row 4, etc, for the entire class.

Barb

 

[Guest]

You are right. That is what I am trying to do keeping the same headers.

 

[Tom Ogilvy]

Sub PrintData()
Dim sh As Worksheet, rng As Range
Dim numcols As Long
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
Set rng = sh.Range(sh.Cells(3, 1), _
sh.Cells(3, 1).End(xlDown))
numcols = sh.Cells(1, 256).End(xlToLeft).Column
sh.PageSetup.PrintArea = rng.Resize(, _
numcols).Address(1, 1, xlA1, True)
sh.PageSetup.PrintTitleRows = _
sh.Rows(1).Resize(2).Address(1, 1, xlA1, True)
sh.PageSetup.Orientation = xlLandscape ' or xlPortrait
rng.EntireRow.Hidden = True
For Each cell In rng
If cell.Row <> 3 Then
cell.Offset(-1, 0).EntireRow.Hidden = True
End If
cell.EntireRow.Hidden = False
sh.PrintPreview
Next
rng.EntireRow.Hidden = False
Next
End Sub

 

[Guest]

Other than writing a macro you have two other options

1) Use MS Word and do a mailmerge using your Excel list as the soource
2) Create a simple form in Excel as follow

Assume you have your sheet set up as follows

A B C D E
1 1 Name Project1 Project2
2 2 John 75% 85%
3 3 mark 65% 25%
4 4 Lisa 55% 78%
5 5 Rahul 80% 68%


I have added the numbers in colum B.
On a new sheet setup the first rows (labels) as you want them, in cell A1
put 1
then beneath them where you want the data enter this formula

= Vlookup(A1,Sheet1!A$1$:$E$5,2,false)
from the example above this will return the name of the student, in the next
cell if you enter the same formula but replace the 2 with 3 it will return
the result for project 1, a 4 will return the result for project 2.

changing the 1 in cell A1 to 2 will return the data for the student 2 and
so on.
You could add a scroll bar and link it to A1 to make it more sophisticated.

Of course you would need to change the above to suit your needs, but should
do what you need.

If you need any further help post back.

 

[Guest]

Assuming:
Headings are in Rows 1 and 2
Student names are in Col A

Try this:
•Select Cell A3
•From the main menu: Select Insert>Page Break
•Select Cell A4
•Press the [F4] key (that will repeat the last action-->setting Page Break)
•Continue selecting subsequent cells and pressing [F4] until no more student
names

•From the main menu: File>Page Setup
•Click the Sheet tab
-Set Rows to Repeat at top to: $1:$2
-Set Print area to the rows with students
-When done click [OK]

•Click the Print Preview button or select File>Print Preview

You should see Page 1 of (some number of page) and each page should only
contain the headers and one student's record.

Does that help?

••••••••••
Regards,
Ron

 

[Guest]

All of you have good suggestions. I am going to try the mail merge first
because it seems the easiest. However, I am also going to try the other
solutions because they seem that they may be better in the long run.

Thank you for your time.

Sarah

Assuming:
Headings are in Rows 1 and 2
Student names are in Col A

Try this:
•Select Cell A3
•From the main menu: Select Insert>Page Break
•Select Cell A4
•Press the [F4] key (that will repeat the last action-->setting Page Break)
•Continue selecting subsequent cells and pressing [F4] until no more student
names

•From the main menu: File>Page Setup
•Click the Sheet tab
-Set Rows to Repeat at top to: $1:$2
-Set Print area to the rows with students
-When done click [OK]

•Click the Print Preview button or select File>Print Preview

You should see Page 1 of (some number of page) and each page should only
contain the headers and one student's record.

Does that help?

••••••••••
Regards,
Ron

I am a teacher. I have created my gradebook in excel. (I don't have
access). I have 7 worksheets (one for each class that I teach). The
worksheets are not exactly the same.

I would like to be able to print out the top two rows (column labels) with
each students data (row). This way the student can see row 1 (name of
assignment), row 2 (points), their row (their personal data for each
assignment).

Any help??