How to source data from multiple Sheets
How to combine multiple sheets into one data merge using IMPORTRANGE or ARRAYFORMULA functions
Last updated
How to combine multiple sheets into one data merge using IMPORTRANGE or ARRAYFORMULA functions
Last updated
Sometimes you might need to pull data from multiple Google Sheet tabs or multiple Google Sheets files. This guide will show you how to set up a Google Sheet tab that will work with Portant and combine information from multiple Sheets into the same Google Sheet tab.
In this article, you’ll learn how to:
Set up a Google Sheet Source that combines data from multiple sheets/tabs
Add formulae to sheets that don't display an error when blank
Use an alternative like Coefficient that’s much simpler than formula combination to sync live data from multiple sheets (and keep your datasets live, on refresh straight from the source)
To set up a workflow that uses data from 2 or more different sheets, you need to combine all the data into one Google Sheet tab. This could be a new tab in an existing spreadsheet, or you could make a new spreadsheet to combine all the data into.
You can import data from other spreadsheets using the ImportRange formula, you can use this formula to import single cells or ranges:
To import cells A1 sheet 1 of the abcd123abcd123 spreadsheet, you enter: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1")
To import cells A1 through C10 from sheet 1 of the abcd123abcd123 spreadsheet, you enter: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")
The first time you connect to a sheet, you may need to allow access, you only need to do this once per spreadsheet, and then all other formulas referencing this sheet will work.
You can also import data from the same spreadsheet using the Array Formula formula, you can use this formula to import ranges of cells from other tabs:
To import cells from columns A through to C from sheet 1 into another tab in the same spreadsheet (for all rows), you enter: =ARRAYFORMULA("sheet1!A:C")
After you have combined all the data into one spreadsheet using the =ImportRange() or =ArrayFormula() functions, you are ready to connect the spreadsheet to Portant.
Sometimes you may need to copy a formula on multiple rows for future data to be entered. When you copy the formula, the blank rows can sometimes display an error or a zero, which can interfere with Portant. To avoid these errors, you can use the following formulae.
To avoid errors, you can use the IFERROR formula: =IFERROR("no error", "error") Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent e.g. =IFERROR(A2*100, "")
To avoid zeros, you can use the IF formula: =IF(logical_expression, value_if_true, value_if_false) Returns one value if a logical expression is 'TRUE' and another if it is 'FALSE' e.g. =IF(A1="","",A1*100))
You can also combine these formulas for a Full-Proof way of preventing Errors or "0" values. =IFERROR((IF(A1="","",A1*100)),"")
Coefficient makes linking multiple Google Sheets data a lot easier, more straightforward, and automatic.
With Coefficient, you can choose your Google Sheets data source file, add filters to refine the information you want to pull in, and import the data with a few clicks, and use GPT Copilot to write formulas or build pivots and charts – all from within one easy-to-use Google Sheets extension.
Updating your linked spreadsheets is also a breeze since Coefficient lets you configure auto-refresh schedules. Not only do these auto-refresh schedules allow you to keep your Google Sheets synchronized, but they also allow you to power each sheet with live data directly from your source (and keep it on refresh), whether it’s your CRM, accounting platform, and more.
All of this means – no more manual updates when your data changes or gets new information. Here’s a quick 2-minute video walkthrough of the process.
We’ll walk through a similar example below, with images and step-by-step details.
Importing Data from Another Google Sheet with Coefficient
Let’s go over how Coefficient can help you combine multiple spreadsheets into one using an easier and more streamlined process.
You can get started with Coefficient by submitting your email here.
Once you have Coefficient installed, click Import Data on the Coefficient pane.
Then, select Google Sheet.
Choose any Google Sheet you want from your Google Drive or other documents you have shared access to.
For this example, let’s pull up the same User Subscriptions list we used in the previous steps and click Select.
Coefficient takes a few seconds to access the data and read the column information. You’ll then see an import panel and the option to filter the data as it comes in.
Once you’re done, click Import.
There you have it. Coefficient imports your data almost instantly, and you don’t even need to deal with functions and formulas throughout the entire process.
So maybe now you’re wondering, “How can I get Google Sheets to auto-update a reference to another sheet?”
Coefficient has you covered since you can automatically refresh that data on a schedule, so you won’t need to do it manually.
The IMPORTRANGE solution allows you to import entire columns at once, but you’ll need to specify bounds for it or it will disrupt your dataset if your data moves too much.
Also, IMPORTRANGE doesn’t offer a built-in way to filter data, so you’ll need to do this separately.
Coefficient takes care of both problems for you by building the filter into the import, including importing any populated rows or columns seamlessly.
If you’re looking to build reports based upon the multiple tabs of data you’ve pulled into one sheet, you can take advantage of GPT Copilot in Coefficient, which can build formulas, pivots, and charts for you. Here’s an example of turning simple text instructions into formulas.
Formula Builder will return a formula like below.
And just like that, you’ve blended data from multiple sources and analyzed it, all without ever leaving Google Sheets!
We created Portant in 2021, and the feedback we have received since then has been very helpful and greatly appreciated. If you have any feedback, please feel free to send us an email at contact@portant.co
