What Is Reference Another Sheet in Google Sheets?
Reference another sheet in Google Sheets refers to getting data from one sheet to another within the same spreadsheet. It is beneficial for managing complex data sets and creating workbooks that can be automatically updated. There are different ways you can reference another sheet. The simplest way to reference data from another sheet is as follows: =SheetName! cellreference. For example, if you wish to reference cell A5 from the ” Cost ” work sheet, you would use the formula =Cost! A5.
Now, let us look at how to reference another sheet in Google Sheets using cell references. For example, here we are referencing cell A1 in a sheet Sheet2 from a sheet named “Sheet1.” Here, we must type =Sheet1!A1. Select the cell in the current sheet where you want to display the data from the other sheet.
If the sheet name contains spaces or any special characters, you can enclose it in single quotes, like this: =’Sheet Two’!A1.
Key Takeaways
- Reference Another Sheet in Google Sheets allows you to access data from one spreadsheet to another in the same workbook or even from different workbooks.
- Referencing helps organize data, perform calculations, and maintain large datasets.
- The simplest way to reference cells or ranges in another sheet is through the format SheetName!CellReference. For example, Sheet1!A1.
- You can also use other methods, such as the FILTER function, to reference data from another sheet based on certain criteria.
- Use IMPORTRANGE to reference data from different workbooks. The syntax of the function is =IMPORTRANGE(“spreadsheet_url”, “range”).
- The size of the ranges you can reference is unlimited. However, performance may be affected if the range is too large or complex.
How to Reference Another Sheet in Google Sheets?
There are different ways to move data seamlessly from one sheet to another. Let’s discuss the most used method.
From Another Sheet (tab) of same workbook
- Open the sheet from which you want to get the data.
- Now, go to your current sheet and select a cell.
- Type = followed by the sheet name, an exclamation point, and the which you are going to copy.
- For example, =Sheet1!A1 or =’Sheet Cost’!B4 (If the sheet name contains spaces or other symbols, enclose it in single quotes.)
From Another Sheet of same workbook Using FILTER function
Google Sheets link to another sheet based on criteria
- Suppose you want to filter a dataset and import the filtered values to another sheet. In such a scenario, you can use the FILTER function. The syntax of the function is as follows:
=FILTER(data_set,criteria_1, criteria_2,…)
data_set – range of cells to filter.
criteria – the criteria that are used to filter the data set.
- For example, to filter your dataset in a sheet called Sheet3 by the name “Peter,” and copy the values from Column B in Sheet2.
=FILTER(Sheet3!B2:B7,Sheet3!A2:A7=”Peter”)
From different workbook using IMPORTRANGE function
To reference another sheet in a Google Sheets spreadsheet and import data, follow these steps.
- Go to the spreadsheet you want to export data from and copy its URL.
- Open the sheet to which you need to upload data.
- Place your cursor in the cell where you want your imported data to be displayed.
- Use the following syntax of the IMPORTRANGE function.
=IMPORTRANGE(“spreadsheet_url”, “range_string”)
Where spreadsheet_url is a Google Sheets link to another sheet
range_string defines what sheet and range to upload data from.
Here, we have imported the data in Column A of Sheet3 onto Sheet4 using the URL of Sheet3.
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1cVnPXWv5vveyGnmfxdT_FiVGdC8KLBqoZOS-HvDPkGk/edit?gid=1442262901#gid=1442262901″,”Sheet3!A2:A7”)
Between Multiple sheets of same workbook using hyperlinks
To create hyperlinks between multiple sheets in the same Google Sheets workbook, you can use the following steps:
- Select the cell you want to add the link to.
- Next, click on Insert -> Link.
- Here, choose the following, “Select a range of cells to link.”
- Select the range of cells/sheet to link to and Click Apply.
- Here, we have selected the link to Sheet3 and added it to Sheet4.
If the sheet name contains spaces or non-alphanumeric symbols, you should include single quotes around it.
Linking Google Sheets can be helpful in consolidating information from multiple worksheets into one or for creating a backup spreadsheet.
Examples
There are many ways to reference data in Google Sheets. You can reference another range or sheet or required rows and columns in different ways. You can also import data from one sheet/spreadsheet to another based on certain criteria. Let us look at all the different options using the examples below.
Example #1 – Sum total sales from the “Sales” sheet of the same workbook
Below is an exciting example where we look at how to extract the sales data from a sheet in the same workbook and sum the data. We have a worksheet called “Sales” containing the sales details of laptops in a store on particular dates. Let us reference “Sales” from another worksheet in the same workbook and sum the data.
Step 1: Let’s try to get this data into another sheet called “Final.” For this, let’s use the following simple syntax in Final.
Sales!B2:B in cell A1 of Final.
However, we need the total value using SUM. Let us wrap it with the SUM function.
=SUM(Sales!B2:B).
Press Enter.
You get the sum of the laptop sales in sheet “Sales” onto sheet “Final.”
Example #2 – Filter items with stock levels below 10 from “”Inventory”” sheet of same workbook
In this example, we have stock information for some items in a store. We have to filter and pick those items with stock below 10. Let’s look at how to do it.
Step 1: Here, we want to filter a dataset from a sheet called “Inventory” and import the filtered values to another sheet. In such a scenario, you can use the FILTER function. Look at the data below.
Step 2: This dataset is in a sheet called Inventory. To filter your dataset by stock levels less than 10, we have to use the following function and copy the values from Columns A and B in “Inventory.”
=FILTER(Inventory!A2:B7,Inventory!B2:B7 < 10).
This function is entered into the sheet Stock.
Step 3: Press Enter. The data from the inventory sheet with stock levels of less than ten are displayed in Stock.
Example #3 – Pull sales data for each month from a different workbook
To pull sales data from different workbooks into a Google Sheet, we can use the IMPORTRANGE function. Let us look at how to do it
Step 1: First, you should ensure that the sheet containing your sales data is shared with the Google account from which you are trying to access it. Now, let us get the URL of the source sheets. Here, we have the sales data for two different months in two workbooks.
Step 2: Use the IMPORTRANGE Function, whose syntax is as below.
IMPORTRANGE(“spreadsheet_url”, “range_string”).
We use this in the sheet “final sales.”
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1RFwI8az5mHjdueZolIboV2t7Tl63yFtZj1n8Mgcrf2s/edit?gid=0#gid=0″,”Sheet1!A2:B5”).
Here, the first argument is the link of the workbook.
The second argument is the Range and sheet name.
Press Enter.
Thus, the January sales data is imported.
Step 3: Now, let us import the February sales data the same way.
Type the following formula in cell A6.
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1qep8MhyfYkOeved1FstbdORqxIBP21KfBjM8_YT2QFM/edit?gid=0#gid=0″,”Sheet1!A2:B5”)
If you get this message asking for access, click “Allow Access.”
Now, check the result.
Thus, you have data imported from different workbooks for different months.
Important Things to Note
- If you wish to handle errors such as missing data while referencing, use the IFERROR function.
- Example, =IFERROR(Sales!A2, “Data not available”)
- Use functions like FILTER or COUNTIF to reference data from another sheet based on specific criteria.
- For simple referencing, you can use ARRAYFORMULA to apply a formula to an entire range. It is helpful if you reference multiple rows or columns.
- Example: =ARRAYFORMULA(Sales!A1:A5 + 2)
- Remember to use referencing updates in real time so that any change in the source will be reflected in the destination sheet.
Frequently Asked Questions (FAQs)
We use options such as direct referencing by mentioning the sheet name and cell number or using functions like FILTER. However, these options work for referencing data between the different spreadsheets of a single Google Sheets document. However, to reference another spreadsheet of another workbook, we must use the IMPORTRANGE function in Google Sheets. This function allows you to import a data range from one workbook to another. Its syntax is as follows:
=IMPORTRANGE(“spreadsheet_url”, “range_string”)
where spreadsheet_url is a Google Sheets link to another sheet
range_string defines which sheet and range to upload data from
Open your spreadsheet in Google Sheets where you want to insert the link.
• No, go to the cell you want to link.
• Then, click on Insert – Link.
• Click on the following: “Select a range of cells to link.”
• Now, select the range of cells that you want to link to.
• Click on Apply.
You will get the #REF error under the following conditions.
1. The sheet name is misspelled.
2. The referenced cell/range does not exist.
3. You’re trying to access a sheet that has been deleted or renamed.
4. To handle this error, you can use the IFERROR function while referencing.
Recommended Articles
Guide to What Is Reference Another Sheet in Google Sheets. Guide to What Is Reference Another Sheet in Google Sheets. We learn how to reference another sheet in different ways with examples. You can learn more from the following articles.
Leave a Reply