Importing data within Google Sheets

Just like excel spreadsheets can be connected together, Google Sheets offer the option to link data. Except it’s a lot better because you don’t need to open both documents to get a live feed.

Uses

Importing data serves three main purposes as far as I can see it:

• It’s a way to share only certain parts of a document
• It can be used to regroup a lot of information in one place, and run reports from there
• It’s can be used to reduce data entry by linking various documents together

How to use the IMPORTRANGE function

The syntax is pretty straightforward:

=IMPORTRANGE ( "sheet url" , "tab name!cell range")

Once you enter this formula in a cell, you will see an error. Select the cell and click the “allow access” button. It’s important to understand that you are giving access to the other document via this spreadsheet. So someone savvy enough would be able to see more than what you are choosing to share.

Typing data in a cell that would usually be filled with imported data will create an error. So make sure to keep the area receiving the information clear.

Sharing parts of a document

Let’s say I’m working on a simple budget in Google Sheets. My workbook consists of 2 tabs, respectively labelled “Budget” and “Cover”. My cover sheet simply imports the totals from the sections of my budget. It’s basically the equivalent of the Movie Magic budgeting cover sheet.

I want to share the cover sheet with my client, so that they always have access to the latest budget estimate, in real time. Here’s the easiest way to do that:

1. Create a copy of your budget, and call it whatever you think is client appropriate
2. In this copy, delete the budget, but keep the tab with the cover sheet
3. Delete all the information contained in the cover sheet, but keep all the formatting. When we import a range, we only import the values, and nothing else. But these values will be formatted based on the receiving document.
4. In cell A1 of your cover sheet, enter the following formula:

=IMPORTRANGE ( "budget sheet url","Cover!1:1000")

This imports the first 1000 rows of our cover sheet, which should be plenty. You can also use columns instead of rows (“Cover!A:Z”)

Finish by click the cell and allowing access. That’s it! you now have a live copy of your cover sheet, and won’t have to export it every time the budget is updated.

You could also use this trick to do the opposite. For instance, you could ask a PA to log all the receipts for a show, and then import the data from their spreadsheet into your budget. This way, your actuals are done as the receipts come in.

Regrouping data for simple cost reporting

In a previous post, I mentioned that using the FILTER function, you can generate cost reports easily, provided that you keep various logs for your expenses. Most companies keep the same logs:

• Accounts payables
• Payroll
• Credit cards / Petty cash

The IMPORTRANGE function lets us regroup all these logs in one place to then search across all the entries. Let’s see how to do that:

1. Create a new document. Call it something like “Cost report tool”
2. Create 5 tabs:
   • Reporting tool
   • AP
   • Payroll
   • PC
   • Formulas
3.  You can use an IMPORTRANGE formula in the top cell of each column, and only import one column at a time to fit any format (just write something like “tab name!A:A” or “Tab name!B:C”). In the AP, Payroll and PC tabs, use the IMPORTANGE formula to gather the following information from each log.
   • Payee
   • Show name
   • Amount
   • Date
4. on the “Reporting tool” tab, we are going to set up a FILTER formula to show us all expenses hitting a specific show. We are going to need:
   • Somewhere for the user to input the show name
   • Ideally the user should be given a list of all the searchable shows
   • A nice way to display the breakdown of costs
   • A subtotal for each section (AP, Payroll, PC) and a final total

Here’s a possible layout for this document:

Each tab contains a 4 column table identical to the ones listed on the “reporting tool”. The “Formulas” tab is just a place for me to list all the shows that will appear in the “show name” dropdown menu. For this, I used the UNIQUE function, which lists all the entries from a range, and discards any duplicates. Dropdown menus created with data validation already ignore duplicates, but I like to make things as clean as possible.

The formulas in this tab should look something like this:

For the sake of this example, I filled each log with some sample data, so I get this result when I enter these formulas.

Going back to the “Reporting tool” tab, I set up my dropdown menu by using data validation (I select columns A through C on the “formulas” tab). I can now see all the shows that are available across all my three logs.

Next, let’s create our filters. In cell A8, I entered the following formula:

=IFERROR ( FILTER ( AP!A:D , AP!B:B = B2 ) )

As we’ve seen in the post about the FILTER function, this will only show lines from the AP log that match the show name entered in cell B2.

Let’s do the same thing for the payroll and PC log:

In cell F8: =IFERROR(FILTER(Payroll!A:D,Payroll!B:B=B2))
In cell K8: =IFERROR(FILTER(PC!A:D,PC!B:B=B2))

Finally, let’s add up the totals of each column, and add these to create the final total. That’s it! We can now generate cost reports in real time at the click of a button.

Note: Based on your payroll log, you might have to add fringes to the result. It can get a little complicate if you have to distinguish between individuals and incorporated loan outs, so I won’t go into it. But it’s doable!

Reducing your data entry with IMPORTRANGE

IMPOTRANGE has many practical uses. One of them could be to link your crew list and your call sheet, so you don’t have to type names twice. In your call sheet template, simply add a tab where you import the names from the crew list of the same project. Then, set up data validation for each cell of the crew section on your call sheet and rejoice! All the names you need will be right there.

Another way to use this function could be to link your entire crew roster to your crew list template. This way, you don’t have to re-type the names of people you’ve already worked with. And if you have a dropdown with their name, it’s then easy to set up a little VLOOKUP to pull their email and phone number as well!

Thumbnail designed by stories / Freepik