Fancy Reading Logs & Formulas

At Star Academy 2nd grade students have an annual book reading challenge. This year it is 70 books by the end of the year with at least 10 being chapter books. Historically, Julie and Maria have asked their students to keep track of their books by completing a Google Form-based reading log. Periodically, they would sort the data by student name and create reports for each student. It was a Sunday night data-sorting and printing nightmare. This year we simplified the process using a few spreadsheet formulas and now each student has their very own live tab were they (or their parents) can easily track reading progress. Interested in doing something similar? Heres how...with screencasts!

Part 1 - Setting Up Separate Tabs
Basically you create a Google Form and create one entry for each student. From there you create a template on the Response spreadsheet using Data Validation and two basic formulas - Query and Count If. Once your template is set you clone a sheet for each student. Scared of the Query formula? No sweat! You can copy/paste the one from the screencast's description area.

Part 2 - Sharing Tabs with Individual Students
Unfortunately, Google does not (yet) have the ability to easily share individual tabs. However you can you can create a new sheet and use the ImportRange formula to provide a live view for each student. Its even easier than Part 1.

The process might sound complicated, but it is actually pretty simple. You might just need a quiet space and about 30-45 minutes to set it up for your entire class, but then you should be set for the year. Shout out to Ted Juch and Google Gooru for inspiring the idea for these reading logs.

Joe