Tech of The Week: Save Time by Linking Data

Data entry can be tedious, so my goal is to only have to enter it once. That’s why I link my Google Sheets together. Click through to find out how I do it and to grab the free cheat sheet!
I use Google Sheets to keep track of a lot of data in my classes. This data has a tendency to become tedious because I gamify my classroom. This means that I have sheets that I use to keep up with XP (experience points students earn each day), another sheet that communicates that information to a Leaderboard, and another that communicates the information to a badge tracker. (Want to learn about gamification? Check out this post!)

To clarify, each time you create a Google Sheet Doc, it's called a Workbook. You can create different Sheets within a single Workbook, and it's simple to have them communicate with each other.

I could just use one Workbook and have separate sheets within that workbook. It's easy to link data that way and you don't have to worry about linking separate workbooks. The problem is, I use Flippity Templates for the Leaderboard and Badge Tracker, so they have to be in separate Workbooks. I show you how to use Flippity HERE.

Here's what the workbook looks like:
Data entry can be tedious, so my goal is to only have to enter it once. That’s why I link my Google Sheets together. Click through to find out how I do it and to grab the free cheat sheet!

Here's how Flippity makes it look online:
Data entry can be tedious, so my goal is to only have to enter it once. That’s why I link my Google Sheets together. Click through to find out how I do it and to grab the free cheat sheet!

My goal is to only have to put the points into the XP Tracking Workbook every week, have it add up the total for me, and then automatically communicate that information to the Leaderboard Workbook so that I don't even have to open that one.

Here's what I do (be sure to grab the free cheat sheet for reference):

1. Open the Workbooks I want to link.
2. Be sure the final column on the XP Tracker is set to auto add the total of each of the weekly rows.
3. Grab the URL from the top of the XP Tracker Workbook.
4. Go to the Leaderboard Workbook. Click on the desired cell (in this case, I'll be doing it for each cell in column C).

Type:
=IMPORTRANGE("paste URL delete back to edit" , "Sheet1!column letter row number")

Sample:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1XkDkH86zahrmy3tpuXMIP9SkCuECyl3v2gXElMPPqoQ/edit", "Sheet1!AL2")


Data entry can be tedious, so my goal is to only have to enter it once. That’s why I link my Google Sheets together. Click through to find out how I do it and to grab the free cheat sheet!

5. Copy that information from the first cell and paste it into every other cell you will be using in that column.
6. Go back through and edit each cell to reflect the column letter and row number you want it linked to. (You will probably only be changing row numbers and probably in numerical order straight down.) This part is tedious, but there is a SHORTCUT:
-Click the first cell you entered the information into.
-Place the cursor on the lower right corner of the cell.
-Drag it down to all of the other cells you want to populate.
-Go back and check--sheets should have auto updated for you AND changed the linked cells in order (2, 3, 4, 5, 6, 7...).

You can even use these sheets again next year—just delete the points and change the student names!

Now when you enter your weekly data, your Leaderboard will automatically update!

Data entry can be tedious, so my goal is to only have to enter it once. That’s why I link my Google Sheets together. Click through to find out how I do it and to grab the free cheat sheet!
Print This for Reference When You Try It!
How do you deal with all of your data? Do you have any hacks to share? Let me know in the comments!
Data entry can be tedious, so my goal is to only have to enter it once. That’s why I link my Google Sheets together. Click through to find out how I do it and to grab the free cheat sheet!

And don't forget to check out last week's Tech of The Week: Make The Internet Your Classroom with Insert Learning.


Get My Notes!

* indicates required
Email Format

12 comments

  1. My subject of intrigue was air dynamic information accumulation and investigation – commonly the amount of speed and flight is produced and how we can improve it. data science course in pune

    ReplyDelete
  2. Well, the most on top staying topic is Data Analytics. Data Analytics is one of the most promising technique in the growing world. I would like to add Data Analytics training to the preference list. Out of all, Data analytics course in Mumbai is making a huge difference all across the country. Thank you so much for showing your work and thank you so much for this wonderful article.

    ReplyDelete
  3. Such a very useful article. I have learn some new information.thanks for sharing.
    data scientist course in mumbai

    ReplyDelete
  4. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data science course in mumbai

    ReplyDelete
  5. Such a very useful article. Very interesting to read this article. I have learn some new information.thanks for sharing. ExcelR

    ReplyDelete
  6. Very nice blog here and thanks for post it.. Keep blogging...
    ExcelR data science training

    ReplyDelete
  7. Attend The PMP Certification From ExcelR. Practical PMP Certification Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The PMP Certification.
    ExcelR PMP Certification

    ReplyDelete
  8. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    ExcelR data science course in mumbai

    ReplyDelete

Back to Top