Generate your own Automated Report Card with these formulas

Automate your Report Card with these easy Google Sheet formula. 

In this lesson you will learn how to use the formula INDEX and MATCH in Google Sheet. In order for you to do the tasks, I provided a sample TEMPLATE of SUMMARY and REPORT CARD which we are using in our centralized grading system in our school. 

Here are the Steps:

1. Open your Summary in Google Sheet. In our school we already have our own template for SUMMARY Grades. You can download our template below. 

SUMMARY GRADES TEMPLATE - DOWNLOAD HERE

Once you open the TEMPLATE, Make a copy of it and save it on your Shared Drive. 

This template comes with SF1, Report Card, SF5 and SF10 already. 

2. Go to CARD Template and Select cell range AH6:AJ7. This is a merge cells. This where we will put our data validation. After this, open Tools and click Data Validation. 

3. In Data Validation Window, select List from a range under Criteria

4, In the Data Validation  window, make sure that the Data Range is looking for the column where the LRN is listed. In this case it is located in Worksheet SUMMARY FINAL GRADES and Cell Range B13:B93. Click OK and click Save to save the data validation. 

5. At this point, when you click the drop down arrow, you should see the list of LRN of your students. This LRN is linked to SUMMARY FINAL GRADE Sheet. 

6. Next let us create the formula to get the data of stuents when we select a specific LRN in the drop down list. Select the cell Address for student's name (D10:G10). A merge cells to house the surname of the student. 

=INDEX('SUMMARY - FINAL GRADES'!C13:C93,MATCH($A$8,'SUMMARY - FINAL GRADES'!$B$13:$B$93,FALSE),1)

In the formula, you could see that INDEX and MATCH is used together to perform more advanced and dynamic lookups. 

7. Repeat the process to other data needed in your card. Just change the cell range of the index that correspond to the data needed. 

If you are interested about this template and automation in school forms, you may contact me by commenting below.