Quick Guide
How to reference a cell from another sheet in Google Sheets:
Step 1: Open the sheet to import the data
Step 2: Select the cell to display the data
Step 3: Type in the formula =sheet_name!cell, eg, Sheet1!A2
When you create a Google Sheets file, you can add any number of sheets. You can also reference a cell from another sheet in Google Sheets when creating charts in the main spreadsheet.
Referencing a cell from one sheet to another allows you to access and move the data in a cell from another sheet. This is useful if you want to create a formula that uses information from two different sources or if you want to use the same data in more than one place in your spreadsheet.
- Why reference another sheet
- How to reference a cell from another sheet
- Reference a cell from another sheet using the FILTER function
Why reference a cell from another sheet in Google Sheets?
Multiple data sheets are accessible from the same spreadsheet. You can also reference a cell on another sheet in a different spreadsheet file. It is also similar to the use of Indirect reference in Google sheets.
Say you have a budget spreadsheet that includes monthly income and expenses. When creating charts, referencing cells from other sheets is less difficult than typing them in manually.
This article will show you how to do that by referencing another sheet with simple examples.
How to reference a cell from another sheet in Google Sheets
Google Sheets is a powerful tool that can be used to make your life easier. If you are looking to get some data from another sheet, this guide will show you how to do it.
To reference a cell from another sheet in Google Sheets, we’d use the following generic syntax:
=Sheet_name!cellFor example, to reference cell A1 from a sheet named Employees, the syntax would be
=Employees!A1.
If the sheet name has spaces or special characters, we enclose the name with inverted commas. For example: =’Employees List’!A2:A100
We can, of course, use the function to do more complicated tasks. So that is what we are going to learn here.
Consider the following data set containing the order history of different products, types, quantities, and delivery statuses. But the price details are missing, which are in another sheet.
Shown below are the price details in another sheet.
We can easily copy this information to the first sheet by referencing the cell.
Here’s how to do it.
Step 1: Go to the sheet you want to pull data from and take note of the cell that contains the data and the sheet name. In this case, the cell starts from B2, and the data is in Sheet2.
Step 2: Click on the cell to which you want to copy the data and enter the formula. The formula is =Sheet2!B2.
Step 3: Press Enter. And you will see the product price on the cell you want it to appear.
Step 4: To fetch the price for the rest of the products, drag the cell containing the formula to the last cell you want to apply the formula.
Below is the result.
Reference a cell from another sheet using the FILTER function
The FILTER function is another method to reference a cell from another sheet in Google Sheets. This FILTER function also uses the LEN function to pull data from multiple sheets.
For Instance, if you are trying to pull the destinations of products delivered from Sheet3 to Sheet1, this formula can come in very handy.
Below is a simple guide on how to do this;
Step 1: Enter the formula in the column you want the data to appear.
The formula is
={ FILTER (Sheet3!B2:B9, LEN(Sheet3!B2:B9))}Where the FILTER + LEN functions pull data from ‘Sheet3’ and populate it in ‘Sheet1’, this function extracts the data from ‘Column B’ in ‘Sheet3’ and places them in your desired cell range.
Step 2: Press Enter. All the list of destinations will appear on the sheet.
We need to note that referencing cell from another sheet is not the same as copying the data; it’s simply displaying the data from the referenced sheet. So, making changes to or deleting the data in the parent sheet will be reflected in the other sheets as well.
Conclusion
Referencing a cell from another sheet in Google Sheets is a simple but useful function. It lets us easily access data without having to switch between different sheets.
We can also reference a cell from a different spreadsheet file. For that we use the IMPORTRANGE function.
See Also
You might find our other articles helpful if you are interested in learning more about Google Sheets. We provide helpful tutorial guides on Google Sheets to help you organize your data.
Here are some other articles that you may find helpful:
How to Use The Querry Function With IMPORTRANGE In Google Sheets
https://blog.tryamigo.com/vlookup-from-another-sheet-in-google-sheets/