Mehrab Imtiaz
Today, we will look at all the ways we can reference cell in another sheet in Google Sheets. We will first touch on the fundamentals of referencing cells then move onto some scenarios where we extract data from another worksheet, both from the same and different spreadsheet.
3 Ways to Reference Cell in Another Sheet in Google Sheets
1. The Basic Way
We start with the simplest way to reference cells in another worksheet in Google Sheets to understand the fundamentals.
Like everything in Google Sheets, any task or function follows a specific syntax, and referencing cells is no different.
To reference cell from another worksheet, we have to follow this syntax:
sheet_name!cell_referenceTo show the process, we will use the following simple worksheets. The first worksheet, Main, is where we will show the cell reference (data extraction) from the other worksheet. The second worksheet, Shop 1, contains the data that is to be extracted.
Main Worksheet
Shop 1 Worksheet
Reference a Single Cell from Another Worksheet
Let’s say we want to reference the first name of the table in the Shop 1 worksheet. The idea is to replace the placeholders in the syntax accordingly.
The first name in the Shop 1 worksheet is in cell B3.
So, our formula in the main worksheet will be:
='Shop 1'!B3
While you can manually write the sheet_name and cell-reference, you can also type “=” and navigate to the other worksheet to select your desired cell to reference it. Press ENTER to complete.
Reference a Range of Cells (with formula)
As convenient as it would be, selecting multiple cells this way does not display the values selected.
To display all the values, we need the help of other functions that we will see in Section 2 of this article, or display it as an array. But you should know that the values are indeed extracted and saved in memory. Simply enclose this formula within curly braces {}:
={'Shop 1'!C3:C12}
We can also use a formula to utilize them. In this case, we are using SUM to calculate the total Sales from the other worksheet.
The formula:
=SUM('Shop 1'!C3:C12)
With that, we have covered the fundamentals of how to reference a cell in another sheet in Google Sheets. The rest of the article will focus on using all that we have learned in different scenarios.
Read More: Indirect Range in Google Sheets (3 Easy Ways)
2. Reference Cell in Another Sheet with Criteria
More often than not, just referencing cells in another worksheet just doesn’t cut it. For complex or advanced spreadsheets, some conditions or criteria might likely be involved.
One such way to reference cells and extract data from another worksheet in Google Sheets is by using the FILTER function.
FILTER function syntax:
FILTER(range, condition1, [condition2, ...])
From a Single Sheet
Let’s first extract data from our Shop 1 worksheet using FILTER. The function allows us to extract a range of cells instead of just a row, column, or just a cell.
To begin, type in the function and apply the range.
Next, apply the condition/criterion for the extraction. In our case, we want all the entries that have sales of more than $500. We first select the column that we will apply the condition on then input the condition (>500)
Press ENTER to see the results.
From Multiple Sheets
We have to get a bit creative if we want to extract data from multiple worksheets since FILTER can only hold one range (one worksheet).
For multiple worksheets we must utilize multiple FILTER functions, we do that by putting them in an array.
To show this example, we have added two more Shop worksheets with a similar layout.
The idea is that each shop will have its own FILTER function presented together as an array.
Our formula:
={FILTER('Shop 1'!B3:C12,'Shop 1'!C3:C12>500);FILTER('Shop 2'!B3:C12,'Shop 2'!C3:C12>500);FILTER('Shop 3'!B3:C12,'Shop 3'!C3:C12>500)}
Since the formula is an array, it is enclosed within curly braces {} and each FILTER function is separated by semicolons (;).
You should also know that a very big advantage of this formula, or the FILTER function in general, is that it is dynamic. Meaning that if the source data is changed, the changes will also be reflected in the extracted data.
But you also have to be careful about putting the wrong type of data in the source since it may result in an error.
Read More: Reference Another Sheet in Google Sheets (4 Easy Ways)
Similar Readings
- Variable Cell Reference in Google Sheets (3 Examples)
- Pull Data From Another Sheet Based on Criteria in Google Sheets (3 Ways)
3. Reference Cell in Another Sheet in another Workbook
For our final example, we will go beyond the scope of our current spreadsheet and to another one.
Our task is the same as we have seen in our first section, but this time, we have to use the IMPORTRANGE function to reference another spreadsheet.
IMPORTRANGE function syntax:
IMPORTRANGE(spreadsheet_url, range_string)Three things to note about IMPORTRANGE:
I. You can use either the whole spreadsheet URL or just the spreadsheet Key for the spreadsheet_url field.
Whole Spreadsheet URL
Spreadsheet Key
II. The function takes only string values. This means that the field values in the function must be enclosed in quotation marks (“”).
III. The function imports all values within the range.
Our formula to extract the entire data table from the worksheet in another spreadsheet:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PhmrVqKnTTSxSow1U4hpQqBDYdNwc23Pa3YerlbVU6c/edit#gid=1563992875","Shop 1!B3:C12")
Like FILTER, IMPORTRANGE is also dynamic, meaning if the source value changes, the value in the main sheet will also change.
Read More: Reference Another Workbook in Google Sheets (Step-by-Step)
Final Words
That concludes all the ways we can reference a cell in another sheet in Google Sheets. We hope that the methods we discussed come in handy in your daily tasks.
Feel free to leave any queries or advice you might have for us in the comments section below.
