Forum Discussion
Attendance Spreadsheet Conundrum
Well, it sounds like you're kind of stuck with that situation. It's entirely possible that Excel's Power Query capability could work with the 85 sheets and deliver what you want. I do not have Power Query on my Mac (or if I do, I haven't been able to access it). So if that could work, I'll hope that one of the more qualified experts around here will chime in.
When the teachers are entering the data for each day, are they working with a sheet that lists all students already, in, say, alphabetical order, or are they also expected to enter the names?
Do all teachers get their attendance reports submitted on time such that you could, each Monday, say, produce the report for all prior periods?
How often are you expected to produce that output report you talk of: is it weekly?
If so, you could just do a copy and paste of the prior seven days and start building a single comprehensive database for all days in the past. It could look something like the attached. You'll see that I moved the names to the columns following the ID, but the only other change was to add a column for date.
If that's feasible, if you could create the consolidated sheet from the individual day sheets, then my next question would be "Do you have the most current version of Excel?" If you do, there are some new functions (notably FILTER) that could come in very handy for extracting the names of students who are exhibiting dangerous numbers of absences.
Why 85 different sheets? I know, each sheet represents a different day. But there are other ways to reflect the day (to be specific, just have a column that contains the date).
The reason I ask is that Excel excels at taking a single database and extracting the kind of data you're looking for, the kind of summary on a given student or class or..... you name it.
I don't know what the logistics are of capturing all the information currently for the students who are attending "virtually," but it sounds like something of a nightmare, however it works.
If the ONLY thing that really matters is "unexcused absences" would it make sense to ONLY track those,, i.e., only make an entry for those. The absence of a record would mean attendance in that case, and would not require an entry. Or do it the other way around. I can see reasons for tracking both, for being "more granular" in your data, but I'm just trying to illustrate the sort of questions that "re-thinking" the process might entail.
Anyway, my first question if we were meeting in person would be "How willing are you to at least consider changing the way you collect data at the Input end of things?" We can deal with the Output later. What you're asking is quite easy if you were to take care of Input by means of a single sheet covering all days.
A single sheet covering all days? What would that look like? I appreciate the response.