Reference data from other sheets in Google Sheets | Easy 2-min Guide

Reference data from other sheets in Google Sheets | Easy 2-min Guide
Reading Time: 4 minutes
Reference Data from other sheets in Google Sheets

We will try to reference data from other sheets in Google Sheets including sheets from different spreadsheet file.

Need to reference data from other sheets

If you’re working with a lot of sheets, sometimes you would want to reference data from other sheets, like you reference data from the same sheet. This is so that you can tally or perform operations of the data from other sheets. For example, one sheets might contain the sales data and you want to reference that data for calculation in the tax sheet. But referencing data from other sheets(from the same or different spreadsheet) can be a bit tricky.

The two problem statements mentioned above need to be handled in different ways. We will be looking  into these in this article

Reference data from other sheets in the same spreadsheet

Sample table
Figure 1: Sample table

We have a list of books in one sheet(sheet name: “books”) and we would want to reference this into the “books_new” sheet.

To reference a cell from another sheet, the syntax followed is:

=sheetname!cell_number

Referencing one cell from other sheet in the same spreadshee
Figure 2: Referencing one cell from other sheet in the same spreadsheet

Referencing the A1 cell gives us the value for that cell. Now to fetch the entire table you can just select and drag and it will reference the entire table.

Entire table referenced
Figure 3: Entire table referenced

Hence we referenced the entire table into the new sheet. Whatever changes we made to the first sheet, will be reflected in the second as it is referenced rather than a copy.

Reference data from other spreadsheets

To reference data from other spreadsheets we need IMPORTRANGE function. The syntax for the same is as follows.

=IMPORTRANGE(spreadsheet_url, range_string)

=IMPORTRANGE(spreadsheet_url, range_string)

spreadsheet_url: As the name suggests this is the URL for the spreadsheet you want to import data from. This should be either in quotation marks or be a reference to the cell which has the URL stored.

range_string: Should be string in the format “sheetname!cellReference”. If sheet name is not mentioned, it picks up the first sheet by default. This should be either in quotation marks or be a reference to the cell which has the range stored.

Fetching URL of the Google Sheet file
Figure 4: Fetching URL of the Google Sheet file

For demonstration purposes, we made a new spreadsheet called spreadsheet2 and let’s say we want to import data from spreadsheet 1. The first step would be to get the URL for the first spreadsheet. You can get the link by clicking on the Share button on the top right.

Then select a cell and enter the above-mentioned formula accordingly.

Access error while using IMPORTRANGE function
Figure 5: Access error while using IMPORTRANGE function

You might see an error like such. This happens when your file has not shared necessary sharing permissions. If your original file has given the access already then this error wont be thrown. If it does, just click on “Allow Acess”. 

Eventually the entire table would be available on the second spreadsheet.

Referenced table using IMPORTRANGE function
Figure 6: Referenced table using IMPORTRANGE function

Conclusion

Hence we learnt to reference data from other sheets in Google Sheets, whether it be in the same spreadsheet or different. You can also check out official documentation for IMPORTRANGE here.

See Also

You can check out other equally good articles on Google Sheets here.

Get Started with Data Validation in Google Sheets: Learn how to use data validation in Google Sheets. Also, learn about variations in data validation by tweaking parameters.

How to import JSON in Google Sheets: See how to work with JSON using Apps Script Editor

Using Vlookup and Hlookup together: Learn how to perform 2-D lookups by combining Vlookup and Hlookup

Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.

One response to “Reference data from other sheets in Google Sheets | Easy 2-min Guide”

Leave a Reply