Linking Google Sheets: How to Reference Data From Another Spreadsheet | Coupler.io Blog
Technology

Linking Google Sheets: How to Reference Data From Another Spreadsheet | Coupler.io Blog

there comes a time in the life of every google spreadsheets user when they need to reference a certain range of data from another sheet, or even a spreadsheet, to create a combined master view of both. this will allow you to consolidate information from multiple worksheets into one.

another common case may result in a requirement for a backing spreadsheet which would be copying the values ​​and formatting from the source file, but not the formulas. some of the users may also want their master document to be updated automatically, on a set schedule.

Reading: How to connect two google spreadsheets

then if you are having difficulty finding the solution to the above tasks, please continue reading this article. you’ll find tips on how to link data from other sheets and spreadsheets, as well as discover alternative ways to do so. At the end, I will provide a full comparison of the above approaches so you can evaluate and choose.

how to reference data from other sheets or tabs

If you’re interested in excel spreadsheets, then head over to our blog post on how to link sheets in excel.

option 1: how to link cells from one sheet to another tab in google sheets

Use the instructions below to link data between google sheets:

  • Open a sheet in google sheets.
  • Place the cursor in the cell where you want the imported data to appear.
  • Use one of the following formulas:

where sheet1 is the exact name of the referenced sheet, followed by an exclamation point, and a1 is a specific cell from which you want import data.

or

where you put the sheet name in single quotes if it includes spaces or other characters like ):;”|-_*&, etc.

in my case the out of the box formula will look like

Example of linking sheets in Google Sheets

Note: if you want to import the range of cells from one sheet to another, just place your cursor on the cell in your data destination worksheet that already contains one of the above-mentioned formulas (=’Sheet two’!A1 or =Sheet1!A1). Then drag it in the direction of your desired range. For example, if you drag it down , the data from these cells will automatically be displayed in your spreadsheet. The same can be done in any other possible direction of your current document.

option 2: how to link a cell in the current sheet to another tab in google sheets

Follow this guide to query data from the current sheet and from other sheets:

  • Open a sheet in google sheets.
  • Place the cursor in the cell where you want the reference data to appear.
  • Use one of the formulas below. continued:

to link data from the current sheet:

where a1:a3 is the cell range of your current active sheet. use square brackets for this argument.

use the following formula to link to another tab in google sheets:

where sheet1 is the name of the referenced sheet and a1:a3 is a specific range of cells from which you want to import data. use square brackets for this argument.

note: don’t forget to put the sheet name in single quotes if you include spaces or other characters like ):;”|-_*&, etc.

option 3: how to link a column from one sheet to another tab in google sheets

to load the entire column from another sheet:

where sheet1 is the name of the referenced sheet and a:a is a range specifying that it will extract the data from column a. use square brackets for this argument.

in my case the out of the box formula will look like

See Also:  Mpow Wireless Headphones: How To Connect Mpow Bluetooth Headphones? - Gawky Geek

list-of-students-option2-2

Option 4: How to import data from multiple sheets into one column

Let’s review an example when one needs to link data from multiple columns in different sheets into one.

In my example, I have three different tabs with sales data: sales 1, sales 2, and sales 3.

three different tabs with sales data: Sales 1, Sales 2 and Sales 3

My task is to collect all customer names in the sheet called “All customers”.

To do so, I’ll use this formula:

where:

  • “all customers” – is my column name,
  • filter(‘sales 1’!c2:c, len(‘sales 1’!c2:c)> 0) – this expression means that I take all the data from column c of “sales 1”, excluding values ​​that are equal to or less than 0.

See also: Mobile Devices and WiFi | University of Houston-Clear Lake

As a result, I get the names of all my clients from three different sheets gathered in one column.

As a result, I get the names of all my customers from three different sheets gathered in one column.

One of the advantages of this approach is that I can change the names of my data source sheets (where I take data from), and they will automatically be updated in the formula!

see how it works:

As a result, I get the names of all my customers from three different sheets gathered in one column - how it works

Option 5: Import data from one Google sheet to another based on criteria

Let’s say you want to filter your dataset by specific criteria and import the filtered values ​​into another sheet. you can do this using the filter function that was introduced in the previous example. here is the syntax:

  • data_set – a range of cells to filter.
  • criteria: the criteria to filter the data set.

as an example, let’s filter users by country, australia, and import the results to another sheet.

Import data from one Google sheet to another based on criteria

Here is what our formula will look like:

FILTER formula example

Read about the Google Sheets FILTER function to discover more filtering options.

how to reference another spreadsheet/workbook in google sheets via importrange

To reference another google spreadsheet workbook, follow these instructions:

  • Browse to the spreadsheet you want to export data from. copy its url.
  • open the sheet you want to load the data into.
  • place the cursor in the cell where you want the imported data to appear.
  • use the syntax as described below:

where spreadsheet_url is a link from google sheets to another workbook, which you copied earlier where you want to get the information from.

range_string is an argument enclosed in quotes to define which sheet and range to load data from.

for example:

  • use “new students!b2:c” to name the sheet and range to get information from.
  • use “a1:c10” to indicate a range of cells only. in this case, if you don’t define the sheet to import from, the default behavior is to load data from the first sheet in your spreadsheet.

you can also use

if a2, in this case, implies the url of the spreadsheet needed to link the data.

An example of Importrange function

Note: the use of IMPORTRANGE anticipates that your destination spreadsheet must get permission to pull data from another document (the source). Every time you want to import information from a new source, you will be required to allow this action to happen. After you provide access, anybody with edit rights in your destination spreadsheet will be able to use IMPORTRANGE to import data from the source. The access will be valid for the time a person who provided it is present in the data source. For more about this Google Sheets function, read our IMPORTRANGE Tutorial.

In my case, my formula looks like this:

or

because “new students” is the only sheet I have in my spreadsheet.

See Also:  Connect PEX to Copper, Replace Copper with PEX | SharkBite

An example of Importrange function

However, the IMPORTRANGE solution has several drawbacks. The one I would mention relates to a negative impact on the overall spreadsheet performance. You can google for IMPORTRANGE in the Google Community forum to see a number of threads that explain the issue in more detail. Basically, the more IMPORTRANGE formulas you have in your worksheet, the slower the overall productivity will be. The spreadsheet will either stop working or require a lot of time to process and therefore display your data.

how to reference another sheet in google sheets via coupler.io

coupler.io is a tool that allows users to pull data from various sources including other spreadsheets, csv files, airtable and many more, into google sheets, excel or bigquery. you can also use it to link a google sheet to another sheet.

register at coupler.io, click add importer and select google sheets as source and destination app.

google sheets source destination

Name your importer and complete the three steps: source, destination, and schedule.

Don’t have time to read? watch our youtube video on how to install coupler.io and set up a google sheet importer.

source

4-Source-section

  • Connect your Google account, then on your Google Drive, select a spreadsheet and a sheet to import data from. You can select multiple sheets if you want to merge data from them into one master view.

You can optionally specify a range to export data from, for example a1:z9, if you don’t need to extract data from an entire sheet.

See also: How to Wire a Doorbell? (Step-By-Step Tutorial)

jump to target configuration.

destination

google sheets destination

  • Connect to your Google account, then select a file on your Google Drive, and a sheet to load data to, You can create a new sheet by entering a new name.

Optionally, you can change the first cell where to import your data range (a cell 1 is set by default) and change the import mode of your data: replace your previous information or add new rows below the last imported entries. You can also enable the last updated column function if you want to add a column to the spreadsheet with the information about the last update date and time.

click save and run to run the import immediately. if you want to automate the data import on a schedule, complete another step.

schedule

5 backup schedule

Toggle on the Automatic data refresh and customize the schedule.

  • select interval (from 15 minutes to every month)
  • select days of the week
  • select time preferences
  • timetime zone

at the end, click save and run to link your google sheet to another sheet.

note: you can also use coupler.io as a google sheets plugin for faster access to the tool in your spreadsheet. To do this, install it from the google workspace marketplace and configure it as described above.

coupler.io-google-workspace-marketplace

How to reference cell in another workbook in Google Sheets with Coupler.io

coupler.io allows you to not only reference another workbook in google sheets, but also import an exact cell range that only fits the specified range. For example, you want to extract data from the range a1:c8 of one workbook and insert it into the range c1:e8 of another workbook. for this, configure as described above, but also specify the following parameters:

Source Workbook

  • Range – Here you will need to specify the range of cells to import data from. in our example, a1:c8

range source google sheets

  • Cell address / Range of the destination workbook – here you’ll need to specify the range of cells to import data to. In our example, C1:E8
See Also:  Does The Dodge Grand Caravan Have Bluetooth?

range destination google sheets

Click Save and Run and welcome your data in the specified range of cells.

google sheets reference cell range

How it works: Pull data from multiple sheets of a single Google Sheets doc

We have a google sheets document with five sheets containing data about deals from different years: 2016, 2017, 2018, 2019 and 2020:

Five sheets that contain data about deals for different years: 2016, 2017, 2018, 2019 and 2020

Instead of manually copying data from each sheet or building a complex IMPORTRANGE formula, we can simply list all these sheets when setting up a Google Sheets importer as follows:

multiple sheets

Click Save and Run and the data from the sheets will be pulled into our destination sheet. What are the main benefits? You’ll get a column indicating which sheet a data set belongs to. Besides, the title rows from each sheet except for the first one are skipped, so you get a smooth merge of data.

multiple sheets merge result

If you want to do the same using IMPORTRANGE, here is what your formula should look like:

Data pulled from multiple sheets using IMPORTRANGE

It’s important to specify exact data ranges like 2018!A2:EU972, otherwise you’ll get multiple blank rows between the data. And do not expect to get your data right away – IMPORTRANGE works pretty long. In our case, we had to wait a few minutes before the formula pulled in the data.

comparing importrange vs. coupler.io

I’ve created a comparison table below that briefly explains the pros and cons of using importrange vs. coupler.io when connecting data between spreadsheets.

google sheets to google sheets is not the only integration provided by coupler.io.

can i import data in google sheets from another sheet including formatting?

Unfortunately, none of the above options will allow you to import the formatting of the cell(s) when referencing another google sheets book. Google Sheets native import, filter and other logic does not involve actual data transfer. they only reference and display data from the source cells. coupler.io is the only option that copies the data from the source, but only imports the raw data without formatting. at the same time you can use coupler.io to link excel files as well as excel and google spreadsheets.

but you can always use the benefits of the google apps script to create a custom function for your needs. for example, the following script will allow you to transfer data from one sheet or spreadsheet to another:

you have to go to tools > script editor then insert the script into the code.gs file and specify the required parameters:

  • id of source and destination spreadsheets
  • names of source and destination sheets

(if you are importing data between sheets, the source and destination spreadsheet id will be the same)

Apps Script for importing data in Google Sheets from another sheet including formatting

When ready, click “Run” and your data including formatting will be imported into the destination sheet.

note: this solution may not be suitable for your project, so you will need to update the script according to your needs.

It’s time to make a decision!

There is no one-size-fits-all solution, and you have to be careful going one way or the other. Whether you’re looking to link sheets, spreadsheets, create combined views, or back up documents, be sure to consider all the pros and cons of both and choose the right option to achieve the best result.

if you only have a few records in your spreadsheet and small formulas, then you can go for importrange. but when you have a lot of data and there are several calculations in your document, coupler.io will be a more stable solution in this case.

See also: How to connect AirPods to a Chromebook | Digital Trends

Related Articles

Leave a Reply

Your email address will not be published.

Back to top button