I have been keeping my gradebooks in Google spreadsheets for years now. It’s very convenient to access them from different devices such as my chromebook and my tablet. There are lots of advantages to keeping gradebooks in spreadsheets, the most obvious one being automatic calculation of grades, class average, etc. However, when it comes to looking up information about a particular student having all the information in one row is rather inconvenient and cumbersome to show to students or parents.
The solution is to create a separate report sheet transposing the relevant information and possibly pulling in information from different sheets (e.g. different terms, additional note). The new Google Sheets has a variety of features that makes this an easy process. Here are the necessary steps
Create a new sheet in your gradebook and transpose the relevant information. You can do this either using copy transpose if you want to choose specific information or the transpose formula if you want to choose all information from the gradebook:
=TRANSPOSE('Term 1'!1:1)
In the B column next to the name field use data validation (menu data/validation) to allow only the students’ names and to create a dropdown list.
The final step is to look up the matching information from other sheets. This can be best achieved with the index and match functions:
=INDEX( 'Term 1'!$A$2:$Z$34,MATCH($B$2,'Term 1'!$B$2:$B$34,0),ROW())
Finally you can pull in information from other sheets simply by repeating the process for other sheets in the file.
In case that feels a bit overwhelming you can simply use my template
Here is the link to the template. If you want to use it just create a copy in your own Google Drive. Of course you can change the variables and sheet names without losing functionality.
.png)
