Continue in Same Cell in Google Sheets When Opening

There comes a time in the life of every Google Sheets user when you need to reference a certain data range from another sheet, or even a spreadsheet, to create a combined master view of both. This will let you consolidate information from multiple worksheets into a single one.

Another frequent case may raise a requirement for a backup spreadsheet that would be copying values and format from the source file, but not the formulas. Some of the users may also want their master document to update automatically, on a set schedule.

So, if you are struggling to find the solution to the above tasks, keep reading this article. You'll find tips on how to link data from other sheets and spreadsheets, as well as discover alternative ways of doing so. In the end, I will provide a full comparison of the approaches mentioned for you to be able to evaluate and choose from.

How to reference data from other sheets or tabs

If Excel spreadsheets are in your focus, then head on to our blog post about 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 your cursor in the cell where you want the imported data to show up.
• Use one of the formulas below:

=Sheet1!A1

where Sheet1 is the exact name of your referenced sheet, followed by an exclamation mark, and A1 is a specified cell that you want to import data from.

Or

='Sheet two'!A1

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

In my case, the ready-to-use formula will look like

='list of students'!B4

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 cell from the current sheet to another tab in Google Sheets

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

• Open a sheet in Google Sheets.
• Place your cursor in the cell where you want the referenced data to show up.
• Use one of the formulas below :

To link data from the current sheet:

={A1:A3}

Where A1:A3 is the range of cells from your current active sheet. Use curly brackets for this argument.

Use the following formula to link to another tab in Google Sheets:

={Sheet1!A1:A3}

Where Sheet1 is the name of your referenced sheet and A1:A3 is a specified range of cells that you want to import data from. Use curly brackets for this argument.

Note: don't forget to put the sheet's name in single quotes if it includes spaces or other characters like ):;"|-_*&, etc.

Option 3: How to link a column from one sheet to another tab in Google Sheets

To upload the entire column from another sheet:

={Sheet1!A:A}

Where Sheet1 is the name of your referenced sheet and A:A is a range that specifies that you will pull the data from the A column. Use curly brackets for this argument.

In my case, the ready-to-use formula will look like

={'list of students'!B1:E11}

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

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

In my example, I have 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 it, I'll use this formula:

={   "All Customers";    FILTER('Sales 2'!C2:C, LEN('Sales 2'!C2:C) > 0);    FILTER('Sales 1'!C2:C, LEN('Sales 1'!C2:C) > 0);   FILTER('Sales 3'!C2:C, LEN('Sales 3'!C2:C) > 0) }              

Where:

• "All Customers" – is a given name of my column,
• FILTER('Sales 1'!C2:C, LEN('Sales 1'!C2:C)> 0) – this expression means that I take all data from column C of the "Sales 1", excluding the values that are equal or less than 0.

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:

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

Let's say, you want to filter your data set by specific criteria and import the filtered values into another sheet. You can do this using the FILTER function that was featured in the example above. Here is the syntax:

                =FILTER(data_set,criterium1, criterium2,...)              

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

As an example, we're going to filter users by country, Australia, and import the results into another sheet.

Here is what our formula will look like:

=filter('Source data'!A2:A,'Source data'!C2:C="Australia")

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 Sheets workbook, follow these instructions:

• Go to the spreadsheet you want to export data from. Copy its URL.
• Open the sheet you want to upload data to.
• Place your cursor in the cell where you want your imported data to appear.
• Use the syntax as described below:

=IMPORTRANGE("spreadsheet_url", "range_string")

Where spreadsheet_url is a Google Sheets link to another workbook, which you copied earlier where you want to pull the information from.

range_string is an argument that you put in quotes to define what sheet and range to upload data from.

For example:

• Use "new students!B2:C" to name the sheet and range to get information from.
• Use "A1:C10" to state a range of cells only. In this case, if you don't define the sheet to import from, the default behavior is to upload data from the first sheet in your spreadsheet.

You may also use

=IMPORTRANGE(B19, "B2:C6")

if A2, in this case, entails the necessary spreadsheet URL to link data from.

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 :

=IMPORTRANGE("spreadsheet_url","new students!B2:C")

Or

=IMPORTRANGE("spreadsheet_url","B2:C")

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

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 to Google Sheets, Excel, or BigQuery. You can also use it to link Google Sheet to another sheet.

Sign up to Coupler.io, click Add importer, and select Google Sheets as both a source and destination app.

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

No time to read? Watch our YouTube video of how to install Coupler.io and set up a Google Sheets importer.

Source

• 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.

Optionally, you specify a range to export data from, for example, A1:Z9, if you don't need to pull data from an entire sheet.

Jump to the destination settings.

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 (A1 cell is set by default) and change the import mode for your data: replace your previous information or append new rows under the last imported entries. You can also toggle on the Last updated column feature if you want to add a column to the spreadsheet with the information about the last date and time refresh.

Click Save and Run to run the import right away. If you want to automate data import on a schedule, complete another step.

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
• Schedule Time zone

In 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 add-on to have faster access to the tool in your spreadsheet. For this install it from the Google Workspace Marketplace and set it up as we described above.

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 into the specified range. For example, you want to pull data from the range A1:C8 of one workbook and insert it into the range C1:E8 of another workbook. For this, perform the setup as described above, but also specify the following parameters:

• Range of the source workbook – here you'll need to specify the range of cells to import data from. In our example, A1:C8

• 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

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

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

We have a Google Sheets doc with 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:

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.

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

={IMPORTRANGE("1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8","2016!A1:EU30"); IMPORTRANGE("1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8","2017!A2:EU572"); IMPORTRANGE("1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8","2018!A2:EU972"); IMPORTRANGE("1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8","2019!A2:EU1243"); IMPORTRANGE("1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8","2020!A2:EU204")}

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

Below I have put together a comparison table that briefly explains the pros and cons of the use of IMPORTRANGE vs Coupler.io when connecting data between spreadsheets.

	IMPORTRANGE	Coupler.io
Small data volumes	Great!	Great!
Big data volumes	IMPORTRANGE may show errors or keep loading data for a long time.	Great!
Frequency of updates	Great! Almost in real-time	Supports manual (any time) and automatic data refresh: once per 1 hour, 3 hours, 6 hours, 12
Time to process calculations	IMPORTRANGE is a formula and it takes some time to process calculations which may slow down the general performance of a spreadsheet.	No calculations are performed on the spreadsheet side. Coupler.io pulls the static data over to your worksheet.
Performance in spreadsheets heavily loaded by formulas	If the total number of formulas in a spreadsheet (including IMPORTRANGE) draws nearer to fifty, the loading speed and the general performance of the document will deteriorate.	Great! It makes no difference for Coupler.io how many formulas you have in your spreadsheet. It will not slow down your worksheet.
Managing permissions / access to import data	Granting permissions is performed per every IMPORTRANGE formula separately, which makes it difficult to manage them in bulk.	Great! Managing account connections is available under Coupler.io GSheets importer settings. So, you just create one connection and use it across the entire document.
Automatic backup of data	IMPORTRANGE syncs the data source and data destination sheets, showing the live data in the latter. So, once the information in the source disappears, it gets automatically removed from your destination sheet as well.	Great! Coupler.io can automatically backup your data and keep it safe in a destination sheet.

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, neither of the options above will let you import the formatting of the cell(s) when you reference another Google Sheets workbook. The logic of IMPORTRANGE, FILTER, and other Google Sheets native options does not entail the actual transfer of data. They only reference and display data from the source cells. Coupler.io is the only option that copies the data from the source, but it only imports the raw data without any formatting. At the same time, you can use Coupler.io to link Excel files, as well as Excel and Google Sheets.

BUT, you can always use the benefits of Google Apps Script to create a custom function for your needs. For example, the following script will let you transfer data from one sheet or spreadsheet to another:

function importTable() {     // Source spreadsheet   var srcSpreadSheet = SpreadsheetApp.openById("insert-id-of-the-source-spreadsheet");   var scrSheet = srcSpreadSheet.setActiveSheet(srcSpreadSheet.getSheetByName("insert-the-source-sheet-name"));     // Destination spreadsheet   var destSpreadSheet = SpreadsheetApp.openById("insert-id-of-the-destination-spreadsheet");   var destSheet = destSpreadSheet.setActiveSheet(destSpreadSheet.getSheetByName("insert-the-destination-sheet-name"));   destSheet.clear();     // Get data and formatting from the source sheet   var range = scrSheet.getRange(1, 1, 48, 32);     var values = range.getValues();   var background = range.getBackgrounds();   var banding = range.getBandings();   var mergedRanges = range.getMergedRanges();   var fontColor = range.getFontColors();   var fontFamily = range.getFontFamilies();   var fontLine = range.getFontLines();   var fontSize = range.getFontSizes();   var fontStyle = range.getFontStyles();   var fontWeight = range.getFontWeights();   var horAlign = range.getHorizontalAlignments();   var textStyle = range.getTextStyles();   var vertAlign = range.getVerticalAlignments();     // Put data and formatting in the destination sheet   var destRange = destSheet.getRange(1, 1, 48, 32);     destRange.setValues(values);   destRange.setBackgrounds(background);   destRange.setFontColors(fontColor);   destRange.setFontFamilies(fontFamily);   destRange.setFontLines(fontLine);   destRange.setFontSizes(fontSize);   destRange.setFontStyles(fontStyle);   destRange.setFontWeights(fontWeight);   destRange.setHorizontalAlignments(horAlign);   destRange.setTextStyles(textStyle);   destRange.setVerticalAlignments(vertAlign);       // Iterate through to put merged ranges in place   for (var i = 0; i < mergedRanges.length; i++) {     destSheet.getRange(mergedRanges[i].getA1Notation()).merge();   }     // Iterate through to get the column width of the source destination   for (var i = 1; i < 18; i++) {     var width = scrSheet.getColumnWidth(i);     destSheet.setColumnWidth(i, width);   }     // Iterate through to get the row heighth of the source destination   for (var i = 1; i < 27; i++){     var height = scrSheet.getRowHeight(i);     destSheet.setRowHeight(i, height);   }   }              

You need to go Tools > Script editor. Then insert the script in the Code.gs file and specify the required parameters:

• ID of the source and destination spreadsheets
• Names of the source and destination sheets

(If you're importing data between sheets, the source and destination spreadsheet ID will be the same)

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

Note: This solution may not be a fit for your project, so you'll need to update the script whatever you require.

It's time to make a choice!

There is no one-size-fits-all solution, and you have to be careful when going one way or another. Whether you are looking to link sheets, spreadsheets, create combined views or backup documents, be sure to consider all the advantages and disadvantages of both and pick the right option for you to achieve the best result.

If you only have a few records in your spreadsheet and little formulas, then you may want to go for IMPORTRANGE. But when you possess lots of data and there are multiple calculations in your document, then Coupler.io will be a more stable solution in this case.

Back to Blog

Focus on your business

goals while we take care of your data!

Try Coupler.io

johnsontagathe.blogspot.com

Source: https://blog.coupler.io/linking-google-sheets/

Share this post