💬 Using Google Sheet to manage student and teacher workload

Replied to Using Google Sheet to manage student and teacher workload (Alice Leung)

The week numbers are the column headings and the subject names are the row headings. If the Google Sheet is shared, teachers can check the boxes to indicate the week they would like an assessment task for their subjects to be due. The running tally automatically adds up the assessment tasks for a week. This can be used to indicate if particular weeks would have too many assessments due. The check boxes allow teams of teachers to have a discussion to moving assessment due dates and the running tally accurately updates the number of assessments per week. So if assessments have to be moved, it makes the negotiation process more efficient.

Alice, this is a great demonstration of the collaborative power and potential of Google Sheets. In particular, I like the possibilities to query data for different information. For example, I made a copy and had a tinker with your template, creating a query of the particular assignments for a given week generated from a wildcard.

=IF($Q2="Week 1",{QUERY(A4:M58,"SELECT A WHERE C = TRUE")},
IF($Q2="Week 2",{QUERY(A4:M58,"SELECT A WHERE D = TRUE")},
IF($Q2="Week 3",{QUERY(A4:M58,"SELECT A WHERE E = TRUE")},
IF($Q2="Week 4",{QUERY(A4:M58,"SELECT A WHERE F = TRUE")},
IF($Q2="Week 5",{QUERY(A4:M58,"SELECT A WHERE G = TRUE")},
IF($Q2="Week 6",{QUERY(A4:M58,"SELECT A WHERE H = TRUE")},
IF($Q2="Week 7",{QUERY(A4:M58,"SELECT A WHERE I = TRUE")},
IF($Q2="Week 8",{QUERY(A4:M58,"SELECT A WHERE J = TRUE")},
IF($Q2="Week 9",{QUERY(A4:M58,"SELECT A WHERE K = TRUE")},
IF($Q2="Week 10",{QUERY(A4:M58,"SELECT A WHERE L = TRUE")},
IF($Q2="Week 11",{QUERY(A4:M58,"SELECT A WHERE M = TRUE")}
)))))))))))

I then built upon this query to generate a unique list of subjects with an assignment:

=UNIQUE(IF($Q2="Week 1",{QUERY(A4:M58,"SELECT B WHERE C = TRUE")},
IF($Q2="Week 2",{QUERY(A4:M58,"SELECT B WHERE D = TRUE")},
IF($Q2="Week 3",{QUERY(A4:M58,"SELECT B WHERE E = TRUE")},
IF($Q2="Week 4",{QUERY(A4:M58,"SELECT B WHERE F = TRUE")},
IF($Q2="Week 5",{QUERY(A4:M58,"SELECT B WHERE G = TRUE")},
IF($Q2="Week 6",{QUERY(A4:M58,"SELECT B WHERE H = TRUE")},
IF($Q2="Week 7",{QUERY(A4:M58,"SELECT B WHERE I = TRUE")},
IF($Q2="Week 8",{QUERY(A4:M58,"SELECT B WHERE J = TRUE")},
IF($Q2="Week 9",{QUERY(A4:M58,"SELECT B WHERE K = TRUE")},
IF($Q2="Week 10",{QUERY(A4:M58,"SELECT B WHERE L = TRUE")},
IF($Q2="Week 11",{QUERY(A4:M58,"SELECT B WHERE M = TRUE")}
))))))))))))

To do this, I added an additional column associated with the assignments associated with the subjects.

One other thing I wondered is whether this could be achieved with each subject recording their own information and then feeding it all into a master sheet using the IMPORTRANGE formula. I have discussed my use of this elsewhere. Separating the data can be a way of managing the data without stepping on each others toes (or cells).

One response on “💬 Using Google Sheet to manage student and teacher workload”

Leave a Reply

Your email address will not be published. Required fields are marked *