Google Sheets: 3 Ways To Reference Data In Another Sheet (Examples)

How do you reference data in another sheet using Google Sheets?

If you need to reference data in the same Google Sheet, there are two means: the sheet reference syntax using the name of the sheet followed by an exclamation mark and the range (i.e. "Sheet2!A1" ), or the function called INDIRECT . If you need to reference data in an external Google Sheet, there is a function called IMPORTRANGE that can help.

Here are each of these approaches in a little more detail described below.

Reference Data Using Sheet!Range Syntax

The most common approach when referencing data in another sheet within your Google Sheet is to use the sheet reference syntax, which is just the name of the sheet followed by an exclamation mark and then the range.

For example, if you have a sheet with the name Sheet2 and you want to reference cell A1 , you would use the following syntax to fetch that value:

=Sheet2!A1

This is pretty straightforward and easy to remember. If your sheet names contain spaces or any other non-alphanumeric characters, simply wrap the sheet name in single quotes.

For example, if you have a sheet with the name Sheet 3 (note the space character between Sheet and 3 ) and you want to reference range A1:B4 , you would use the following syntax to fetch that range:

'Sheet 3'!A1:B4

This syntax is useful in functions that permit the use of a range, such as the SUM function:

=SUM('Sheet 3'!A1:B4)

Error! Unresolved Sheet Name

If you get a #REF! error in your cell when typing out your sheet name here are a few things you can check to help get your formula working again:

First, check that the sheet’s name is wrapped properly in single quotes, i.e. 'Sheet Name'!A1 . Remember, the closing single quote needs to come before the exclamation mark.

Next double check you have entered the name of the sheet correctly between the quotes, or if the name does not contain non-alphanumeric characters, check there are no spaces or non-alphanumeric characters before the exclamation mark.

Next, check you have entered the range syntax correctly. Sometimes, Google Sheets can prepend the sheet’s name in front of a named range, especially if the same name for a named range is found on more than one sheet.

If this all is still leaving you with scratching your head, try using the next available method below in referencing your data from another sheet.

Reference Data In Another Sheet Using INDIRECT Function

While you can directly enter the sheet’s name into a formula and reference the data from another sheet using the 'Sheet Name'!Range syntax, you also use the INDIRECT function to create this syntax by concatenating all the elements needed together.

As the syntax for fetching data in another sheet is the name of the sheet followed by an exclamation mark followed by the range, you can combine all these elements together into the INDIRECT function.

For example, if you want to reference the range "A1:B4" in Sheet 3 you could use the INDIRECT function like so:

=INDIRECT("Sheet 3!A1:B4")

You’ll notice that this brings in the range of data of A1:B4 from Sheet 3 , but if you did just ='Sheet 3'!A1:B4 in the same cell, you get Error: An array value could not be found .

The INDIRECT formula can be an easier means of working with references as it does what you’re looking for it to do without the side effects.

Reference Data From External Sheet Using IMPORTRANGE Function

If the data is to be obtained from an external Google Sheet spreadsheet, you can use the IMPORTRANGE function , which fetches data from a specific range in the external spreadsheet.

The IMPORTRANGE function contains two parameters: the first being the spreadsheet URL (or a reference to a cell that contains the spreadsheet URL) and the second parameter being the range to import. If no sheet is specified in the second parameter, it is assumed the data to fetch will be from the first sheet.

For example, if the url of the spreadsheet were something like "https://docs.google.com/spreadsheets/d/somerandomstring" and you wanted to import data from Sheet Name!A1:B4 this would be:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/somerandomstring","Sheet Name!A1:B4")

The first time you run this function, you will be required from the destination spreadsheet to permit access. If you see a #REF! error in the cell, just give it a moment while it fetches the data. If you still see the #REF! error in the cell, then hover over it, and you might see further detail about the issues the spreadsheet may have, such as permission problems (i.e. you don’t have access to the spreadsheet URL’s data) or perhaps the sheet name referenced not existing.

Summary

To fetch data from another sheet in Google Sheets, you can use the IMPORTRANGE function for external Sheets, or if the data resides in the same Google Sheet, you can use the sheet reference syntax of 'Sheet Name'!Range or the INDIRECT("Sheet Name!Range") function. Both methods have their own benefits and can help when you get stuck.