Syntax=sheet_name!cell_number
Sample Usage=Employees!B4
//This will reference the value in B4 in the Employees sheet.='Sales in June'!B3:B12
//Values in the range B3:B12 from the sheet Sales in June will be referenced in the new spreadsheet.
Note: To reference cell values from a spreadsheet in another workbook, use the IMPORTRANGE function.
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
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 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.
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.
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.
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.
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.