=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
. 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”
Reposts