Today, we look at how to reference another sheet in Google Sheets. While it does sound easy (and it is), it’s common to see users of all levels of spreadsheet expertise mess up.
This article stands as a trusty guide for those situations. Let’s get started.
Reference Another Sheet in the Same Spreadsheet in Google Sheets
1. Basic Cell Reference to Another Sheet
To show our processes, we have created this workbook containing worksheets of three separate workplaces: Office 1, Office 2, and Office 3. And a fourth worksheet called Master List which we will be populating.
![Office Worksheet - reference another sheet in google sheets](https://officewheel.com/wp-content/uploads/2021/12/1-google-sheets-reference-another-sheet.png)
Office Worksheet
![Master List Worksheet](https://officewheel.com/wp-content/uploads/2021/12/2-google-sheets-reference-another-sheet.png)
Master List Worksheet
a. Single Cell Reference from Another Sheet
Let’s start simple. We will be extracting (referencing) the name of the first employee from Office 1 worksheet to cell B3 of the Master List worksheet.
Step 1: Input equals-to (=) in cell B3.
Step 2: Move to Office 1 worksheet and select the first name there.
Step 3: Press ENTER.
='Office 1'!B3
You can also type in the name of the worksheet you are referring to within single quotes (‘’) and the cell number of that worksheet separated by an exclamation (!).
Thus, our syntax for referencing another worksheet in Google Sheets is:
='worksheet-name'!cell-reference
b. Range Reference from Another Sheet in Google Sheets
Range references work the same way. Instead of typing in a single cell value, we input a range.
Continuing from Step 2 from our previous section, we click and drag down the column of names to select them.
Press ENTER.
But this doesn’t show all the names, does it?
The data has been extracted but it is confined to one cell. To display all the names we have to present the data as an array, thus we have to enclose the formula within curly braces {}.
={'Office 1'!B3:B12}
That concludes the fundamentals of referencing data from another sheet in Google Sheets. From this point onward, we will be looking at the iterations of what we have learned, up to referencing another spreadsheet entirely.
Read More: Reference Cell in Another Sheet in Google Sheets (3 Ways)
2. Import Data From Multiple Sheets into One Column
A situation may arise where you may need to extract data from ranges that belong to multiple worksheets. Similar to our All Employees column in our Master List worksheet, where we want to list the names of all employees from the three offices.
To help us with that, we will utilize the FILTER function.
To extract names from Office 1 we use the following formula:
=FILTER('Office 1'!B3:B12,LEN('Office 1'!B3:B12)>0)
Formula Breakdown:
- ‘Office 1’!B3:B12: Our range of names in the Office 1 worksheet. You can update the range to ‘Office 1’!B3:B to extract the column dynamically.
- LEN(‘Office 1’!B3:B12)>0: Our condition for extraction. It checks whether the value of the cell is greater than 0, returns TRUE if it is.
This formula functions similarly to ={'Office 1'!B3:B12}
of the previous section, up to the point of outputting the data as an array.
Formula for Office 2:
=FILTER('Office 2'!B3:B,LEN('Office 2'!B3:B)>0)
Formula for Office 3:
=FILTER('Office 3'!B3:B,LEN('Office 3'!B3:B)>0)
All we need to do now is combine the different sections with a semicolon (;) separator and enclose it in curly braces {}:
={FILTER('Office 1'!B3:B,LEN('Office 1'!B3:B)>0);FILTER('Office 2'!B3:B,LEN('Office 2'!B3:B)>0);FILTER('Office 3'!B3:B,LEN('Office 3'!B3:B)>0)}
One other advantage of using this method is that it is dynamic. Not only for new data added to each of these Office worksheets but also for the worksheet names. So even if you change the name of the worksheet, it will be reflected in the formula.
Adding a new Employee Name
Changing the worksheet name also changes its name in the formula
Similar Readings
- Lock Cell Reference in Google Sheets (3 Ways)
- Dynamic Cell Reference in Google Sheets (Easy Examples)
3. Import Data With Criteria
To present this method, we will again use the FILTER function, but we will be changing up the scenario.
This time, we will extract names according to the Departments of the Employees.
The modification to the formula is simple:
![Office 1 worksheet](https://officewheel.com/wp-content/uploads/2021/12/1-google-sheets-reference-another-sheet.png)
Source worksheet: Office 1
You can do it for all the departments. The Department name condition can be referred to the header cells.
=FILTER('Office 1'!$B$3:$B,'Office 1'!$C$3:$C=C$2)
Reference Another Sheet From a Different Spreadsheet in Google Sheets
Google Sheets does not restrict us to only reference worksheets from the same spreadsheet. We can also use references from a separate spreadsheet thanks to the IMPORTRANGE function.
The IMPORTRANGE syntax:
IMPORTRANGE(spreadsheet_url, range_string)
We have created a separate workbook containing the Office 4 worksheet
We will be extracting all the names from the Office 4 spreadsheet here in the New Employees worksheet in our main workbook.
Let’s go through the process, step-by-step.
Step 1: Open the function in cell B3, =IMPORTRANGE(
Step 2: Copy the URL of the other workbook and paste it within the function within quotation marks (“”). Press comma (,) to move to the next part
Step 3: Input the worksheet name and the range of cells in the following format:
“Office 4!B3:B12”
Step 4: Close parentheses and press ENTER.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1M0QaYmxGruuvAlRiS5wlntXSltWEByyV1Q2nbrRO1NE/edit#gid=0","Office 4!B3:B12")
Alternatively, we can also use the Spreadsheet Key of the spreadsheet URL in the formula to give the same results.
Spreadsheet Key
New formula:
=IMPORTRANGE("1M0QaYmxGruuvAlRiS5wlntXSltWEByyV1Q2nbrRO1NE","Office 4!B3:B12")
If you want to learn more, here’s an in-depth guide on How to Reference Another Workbook in Google Sheets.
Read More: Reference Another Sheet in Google Sheets (4 Easy Ways)
Final Words
The ability to reference another sheet in Google Sheets is very easy. The only difference you may face, when compared to applications like Excel, is when we reference a different workbook or spreadsheet entirely with its IMPORTRANGE function.
We hope that the methods we have presented in this article help to solve any issues you may face or to just simply understand how referencing works in Google Sheets.
Feel free to leave any queries or advice you may have for us in the comments section.
Related Articles
- Pull Data From Another Sheet Based on Criteria in Google Sheets (3 Ways)
- Reference Another Workbook in Google Sheets (Step-by-Step)
- Return Cell Reference in Google Sheets (4 Easy Ways)
- Indirect Sheet Name in Google Sheets (Easy Steps)
- Reference Another Tab in Google Sheets (2 Examples)
- Relative Cell Reference in Google Sheets
- Variable Cell Reference in Google Sheets (3 Examples)