Hi Excel Programmers:
Described here is functionality that a small school would like added to an Excel app for
tracking student grades and progress. The gradebook computes both objective, academic grades as well as more subjective,
behavioral grades, and is used to track the progress of kids with severe behavioral, emotional, and learning disorders.
In short--teachers would like Excel to create new sheets patterned after “Progress Report Template” for all students
marked “y” and, for a certain date range, copy the students’ comments entered in the “Period” sheets to each student’s newly
made progress report sheet. Any coding help would be greatly appreciated.
The current gradebook application
started 6 years ago and has ballooned into the attached. Thanks to the number of forum participants/programmers for making it
work. Any additional help would be greatly appreciated.
Here are worksheets involved:
“Students” in which last and first names of students are imported into cols A and B. On this sheet are also two Microsoft
Date Time Picker controls for selecting starting and ending report dates.
7 worksheets named Period 1, Period 2,…,
Period 7 (for 7 different school classes). A student may be in 1 or more period. If a student is in multiple periods, he/she
is Not necessarily entered on the same row.
Worksheet named “Progress Report Template” to pattern reports after
for students in a teacher’s caseload.
Here is the setup of the 7 worksheets named Period 1, Period 2,…, Period
Last and first names of students are in columns B and C on every ODD row, starting on row 13.
are inserted in row 12; each day’s date is in a separate column. There are other headings along this row, not just dates, but
the date columns are entered consecutively.
Comments about a student’s work that period are typed in the date
columns, but 1 row Below which the student’s names have been inserted (so the comments are on every EVEN row).
Here is the desired functionality:
Teachers would like to go to the sheet named “Students,” select starting and
ending dates from the Microsoft Date Time Picker controls (E3 and E5), click a blank cell next to a student’s name (to select
that student to report on), say, a cell in col D, and then have Excel do the following:
1. Store the last and
first names of the student from cols A and B as variables.
2. Store the starting and ending dates from E3 and
3. Create a copy of the worksheet named “Progress Report Template” in the workbook.
4. Rename the
copy of “Progress Report Template” with the student’s first and last names.
5. In the student’s new report
worksheet, input his/her last name in cell B1 and his/her first name in B2.
6. In the student’s new report
worksheet, insert the starting date in B6 (or a row below any existing data).
7. Here’s the crazy part – scan
Period 1 for the student’s last and first names (would be in cols B and C on the "Period" sheets). If found, do
8. Copy the typed comment (if any) in the cell at the intersection of 1 row below the student’s
names (cols B and C) and the column with the date heading in row 12 that matches the starting date.
9. Return to
the student’s report worksheet and insert the starting date in cell B6 and the corresponding comment in cell C6 (or a row
below existing data).
10. Then, go to and scan Period 2 and do the same if the student is present—copy the comment
at the intersection of 1 row below the student’s names and the column with the matching date in row 12, return to the
student’s report worksheet and insert the date and comment [a row below the previously used row].
11. Repeat this
for each Period’s sheet.
12. AND—for a range of dates (from the Microsoft Date and Time Pickers in the Student’s
sheet)—repeat this for the next consecutive date—scanning the Period worksheets, copying the comments for the students on
those days, then copying and listing them on the student’s report sheet.
13. Lastly, set the newly inserted dates
and corresponding comments as the new print range (everything above row 5 will be the page’s heading).
very much do I? I realize this is very complex. Again, any help on this would be super appreciated. Thanks.