How to LINK data from one Google Sheet Workbook to another Google Sheet Workbook.

 How to LINK data from one Google Sheet Workbook to another Google Sheet Workbook. 

1. Open the Google Sheet Workbook, in this example, A Google Sheet Workbook of my school forms name as "School Forms and Grades TEMPLATE"

- SCHOOL FORMS TEMPLATE

- ELECTRONIC CLASS RECORD FOR SENIOR HS

2. Make a copy of these two Wotkbook and save it on your own Google Drive Folder. I suggest that you use Google Shared Drive instead of Google Drive. 

3. In School Forms and Grades TEMPLATE, Go to worksheet SF1 and start typing your students LRN in column B and NAMES in column C

4. Here, I want to import the LRN and NAMES located in column B and C of my Worksheet named "SF1". to other Google Sheet Workbook, in this example the Electronic Class Record renamed as "G11_1STSEM_SUBJ1"

5. Copy the URL (Uniform Resource Locator) of the School Forms and Grades TEMPLATE where the SF1 Worksheet is included.

6. You need to take also the Worksheet Name which in this case is SF1 and the Cell Range where the Data (LRN and Names) is located. 

6. In the ECR Worksheet, create a worksheet and name it as VARIABLES. Paste the URL of the School Forms and Grades TEMPLATE and put it inside the double quotation mark. (""). Here we put it on cell B2.

7. Now create another worksheet and name it as INPUT DATA and create the formula as shown below. Type it on the first number for your student. Make sure that the column and rows in School Forms and Grades TEMPLATE will match with your columns and rows here. 

The IMPORTRANGE formula is use to import a range of cells from a specified spreadsheet.

The formula is IMPORTRANGE("URL of the GOOGLE SHEET WORKBOOK","Worksheet Name!Cell Range")

In this example, our Spreadsheet URL is already defined in Cell B2 of the VARIABLES worksheet. 

The Worksheet name is SF1 

The Cell Range where our Data is located is B10:C49

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.