6
6afraidbecause789
Hi - I'm trying to use a solution that Bernie provided earlier that
looks up a student's score data on a sheet and puts it into another
report sheet. I've included my post and Bernie's solution below. The
solution works for the first student (in row 13), but does not work
for any other student from row 14 on down (there is a row between each
student). Is it a problem with referencing or arrays? Any help
would, again, be greatly appreciated.
Thanks
==============================
Hi there - I’ve made a gradebook that other teachers will now use, but
we want to be able to print reports from it. Say if a teacher clicks
anywhere on a student’s row, how can Excel output that student’s data,
found in certain columns in that sheet as well as possibly 6 other
worksheets (7 periods in a day) into Word, another Excel sheet, or
some other format?
If you can help, here’s the layout of the workbook:
8 sheets total, named Period 1, Period 2, …, Period 7, and Fields
(where I put the HREF scores).
Col headings in sheets 1 through 7 occupy rows 8 – 12.
Student last and first names are in cols A and B, starting on row 13.
The same student may be on each Period’s sheet.
There is 1 row under each student’s row that also has data for that
student (2 rows per student).
Values of formulas to output are in cols E through Q, T, AH, and AI.
Values in a range to also output start at AR—a new col is added after
AR for each new assignment; scores are entered in the cells below.
Another range to output starts 3 columns after the last column used
for the assignments. A col will be entered each day with the date in
the header and daily points in cells down below.
Note that these 'ranges' will end up being in different columns in
different periods.
If this is possible, us teachers would also like it if we could output
reports for all students in the entire workbook at once, similar to a
mail merge.
==============================
Solution by Bernie:
I think you should consider a different solution: setting up report
templates for each Period.
Insert a new column A on each sheet, and in that column, enter a
formula like this in A13 (whichever row has your first student name)
=IF(B13="", A12 & " B", B13 & ", " & C13)
That will create a one cell name for each student (and a second for
the second row) that can be used to look up values.
Then insert a new sheet, a report template for that sheet, and name
a cell StudName. Then enter the student name of interest, in the
form
Lastname, Firstname
Then for each report sheet, enter these formulas somewhere in a three
cell block, like A3:A5, and then drag to the right as far as you can.
These formulas will pull data based on the value in StudName
=INDEX('Period 1'!$12:$12,1,Column(D1))
=VLOOKUP(StudName,'Period 1'!$6:$10000,COLUMN(D1),FALSE)
=VLOOKUP(StudName & " B",'Period 1'!$6:$10000,COLUMN(D1),FALSE)
Then, select and drag the cells around the page to lay the values out
the way that you want. Delete the formulas that pull in unneeded
data, and leave the formulas that will pull in data from the future.
Set the page print area, the formatting, and then you can pull in any
student's data at any
time.
Data from all periods can be combined into one report, etc - the key
it that it will all be formula driven, linked to the entered student's
name.
looks up a student's score data on a sheet and puts it into another
report sheet. I've included my post and Bernie's solution below. The
solution works for the first student (in row 13), but does not work
for any other student from row 14 on down (there is a row between each
student). Is it a problem with referencing or arrays? Any help
would, again, be greatly appreciated.
Thanks
==============================
Hi there - I’ve made a gradebook that other teachers will now use, but
we want to be able to print reports from it. Say if a teacher clicks
anywhere on a student’s row, how can Excel output that student’s data,
found in certain columns in that sheet as well as possibly 6 other
worksheets (7 periods in a day) into Word, another Excel sheet, or
some other format?
If you can help, here’s the layout of the workbook:
8 sheets total, named Period 1, Period 2, …, Period 7, and Fields
(where I put the HREF scores).
Col headings in sheets 1 through 7 occupy rows 8 – 12.
Student last and first names are in cols A and B, starting on row 13.
The same student may be on each Period’s sheet.
There is 1 row under each student’s row that also has data for that
student (2 rows per student).
Values of formulas to output are in cols E through Q, T, AH, and AI.
Values in a range to also output start at AR—a new col is added after
AR for each new assignment; scores are entered in the cells below.
Another range to output starts 3 columns after the last column used
for the assignments. A col will be entered each day with the date in
the header and daily points in cells down below.
Note that these 'ranges' will end up being in different columns in
different periods.
If this is possible, us teachers would also like it if we could output
reports for all students in the entire workbook at once, similar to a
mail merge.
==============================
Solution by Bernie:
I think you should consider a different solution: setting up report
templates for each Period.
Insert a new column A on each sheet, and in that column, enter a
formula like this in A13 (whichever row has your first student name)
=IF(B13="", A12 & " B", B13 & ", " & C13)
That will create a one cell name for each student (and a second for
the second row) that can be used to look up values.
Then insert a new sheet, a report template for that sheet, and name
a cell StudName. Then enter the student name of interest, in the
form
Lastname, Firstname
Then for each report sheet, enter these formulas somewhere in a three
cell block, like A3:A5, and then drag to the right as far as you can.
These formulas will pull data based on the value in StudName
=INDEX('Period 1'!$12:$12,1,Column(D1))
=VLOOKUP(StudName,'Period 1'!$6:$10000,COLUMN(D1),FALSE)
=VLOOKUP(StudName & " B",'Period 1'!$6:$10000,COLUMN(D1),FALSE)
Then, select and drag the cells around the page to lay the values out
the way that you want. Delete the formulas that pull in unneeded
data, and leave the formulas that will pull in data from the future.
Set the page print area, the formatting, and then you can pull in any
student's data at any
time.
Data from all periods can be combined into one report, etc - the key
it that it will all be formula driven, linked to the entered student's
name.