Google sheets reference another sheet in formula

Pulling Data from another sheet in Google Sheets is a very simple process. This tutorial will walk through the steps to pull data from other sheets in Google Sheets

Setting up the Data

For this Example, we will use two Sheets. Sheet 1- cell A2 will be the formula cell. Sheet 2 will be the reference Sheet where we will pull the data from. In Column A of Sheet 2, enter some test data to reference in our formula

Figure 1 – Sheets 1 an 2 Data Setup

Applying the formula Single Cell

Now that we have our data setup, the next step is to enter our formula in cell A1 of Sheet 1

Formula Syntax: =‘Sheet Name’!Cell

• In cell A1 of Sheet 1, enter this formula =’Sheet2’!A1

Figure 2 – Applying the Formula in a Single Cell

We can see in this example that by entering the referencing  Sheets name in this format ‘Sheet Name!’, the formula pulled the data associated with Sheet 2 into Sheet 1.

Applying the formula to a Range

Now that we know how to apply the formula to a single cell, we can use the same concept to apply to a range of data. Not only will we apply this formula into a range of data, but we will illustrate how we can nest this formula inside others in order to reference other sheets in any formula

• In Column B of Sheet 2, enter some numeric data
• In cell B7 of Sheet 2, enter this formula =sum(B1:B6)
• In cell A3 of Sheet 1, enter this formula = sum(‘Sheet 2’!B1:B6)

Figure 3 – Applying the Formula to a Range

We can see in this example the formula in Sheet 1, cell A2 sums the data in range B1:B6 of Sheet 2 to match the sum result in Sheet 2, cell B7. This same concept can be applied for any Excel formula

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.

This tutorial will demonstrate how to reference a cell in another sheet in Excel and Google Sheets

Reference to another Sheet – Create a Formula

In a workbook with multiple worksheets, we can create a formula that will reference a cell in a different worksheet from the one you are working in.

Select the cell where the formula should go ex: C7

Press the equal sign, and then click on the sheet you wish to reference.

Click on the cell that holds the value you require.

Press Enter or click on the tick in the formula bar.

Your formula will now appear with the correct amount in cell C7.

The sheet name will always have an exclamation mark at the end.  This is followed by the cell address.

Sheet_name!Cell_address

For example:

=Northern!C10

If your sheet name contains any spaces, then the reference to the sheet will appear in single quotes.

For example:

='Northern Office'!C10

Should the value change in the source sheet, then the value of this cell will also change.

You can now drag that formula across to cells D7 and E7 to reference the values in the corresponding cells in the source worksheet.

Reference to Another Sheet – the INDIRECT Function

Instead of typing in the name of the sheet, you can use the INDIRECT Function to get the name of the sheet from a cell that contains the sheets name.

When you reference another sheet in Excel, you usually type the sheet’s name, and then an exclamation mark followed by the cell reference.  Since sheet names often contain spaces, we often enclose the name of the sheet in single quotes.

In the formula above therefore, we have used the INDIRECT Function to refer to the name of the sheet in cell B7 ex: “Northern”.

The entire formula above would therefore be

=INDIRECT("'" & Northern & "!C10")

where we have replaced the sheet name “Northern” with cell B7.

We can then copy that formula down to C8 and C9 – and the sheet name “Northern” will be replaced with “Southern” and “Western” as the formula is copied down.

Reference to another Sheet – an Array Formula

To reference to another sheet using an Array formula, select the cells in the Target worksheet first.

For example:

Select C8:E8

Press the equal sign, and then click on the worksheet that contains the Source data.

Highlight the relevant source data cells.

Press Enter to enter the formula into the Target worksheet.

You will notice that the cells contain a range (C10:E10) but that each relevant column will only show the value from the corresponding column in the source workbook.

Reference to a Range Name

The array formula is useful when you are referencing to a range name that contains a range of cells and not just a single cell.

In the above example, the total values of the Western office in row 10 is called Western.

Click in the Head Office sheet, highlight the cells required and press the equal sign on the keyboard.

Type the range name that you have created ex: Western.

Press Enter.

An array formula will be created.

Reference to another Workbook

You can also link workbooks together by means of referencing to a cell in another workbook.

Have both workbooks open in Excel.  You can use the view menu to see them both on the screen if you wish.

Click in the cell you wish to put the source data in ex: C12

Press the equal key on the keyboard and click on the Source cell in the different Workbook.

Press Enter.

The formula that is entered into the original sheet will have a reference to the external file, as well as a reference to the sheet name in the external file.

The name of the workbook will be put into square brackets, while the sheet name will always have an exclamation mark at the end.  This is followed by the cell address.

[Workbook_name]Sheet_name!Cell_address

For example:

=[SalesFigures.xlsx]Northern!$C$10

You will notice that the cell reference has been made an absolute.  This means that you CANNOT drag it across to columns D and E unless you remove the absolute.

Click in the cell, and then click in the formula bar and click on the cell address of the formula bar.

Press F4 until the absolute is removed.

Alternatively, you can delete the $ signs from around the absolute.

Drag the formula across to columns D and E.

Reference to another Sheet in Google Docs

Linking worksheets with formulas works the same way in Google Docs as it does in Excel.

Click in the cell you wish to put the formula into, and then click on the Source cell where your value is stored.

Press Enter.

Your formula will now appear with the correct amount in cell C7.

The sheet name will always have an exclamation mark at the end.  This is followed by the cell address.

Sheet_name!Cell_address

For example:

=Northern!C10

Drag the formula across to populate columns D and E, and then repeat the process for all the sheets.

Reference to another Sheet using an Array Formula in Excel

The array formula will also work in the same way.

Highlight the range you wish to put the target information in and press the equal sign on the keyboard.

Click on the Source sheet and highlight the cells you require.

Press Shift+Enter.

The formula will appear as a range but that each relevant column will only show the value from the corresponding column in the source workbook.

Reference to another Workbook in Google Docs

If you want to link Google Sheets files together, you need to use a function called IMPORTRANGE

Open the Source Google sheet file in order to copy the URL address of the file.

For example

Return to the sheet where you wish to input the formula and click in the relevant cell.

Press the equal sign on the keyboard and type in the function name ex: IMPORTRANGE, followed by a bracket and inverted commas.

Paste the URL copied from the source Google sheet into the formula.

Close the inverted commas

For example:

=IMPORTRANGE("//docs.google.com/spreadsheets/d/1kJ9ECcuwO65ayFXCobxuUSwx9MnPSDXypiyPWoHc59E/edit#gid=1711678147"

Add another comma and then also in inverted commas, type in the cell reference required

For example

“Northern!C10”

Your complete formula will look like the example below

=IMPORTRANGE("//docs.google.com/spreadsheets/d/1kJ9ECcuwO65ayFXCobxuUSwx9MnPSDXypiyPWoHc59E/edit#gid=1711678147", "Northern!C10")

When you link Google sheets for the first time, this message could appear.

Click Allow access.

Repeat the process for any other cells that need to be linked eg D10 and E10.

TIP: Drag the formula across to the required cells and change the cells address in the formula bar!

How do you reference another sheet in an equation?

How do I link one sheet to another in Google Sheets?

How do I pull data from another sheet in Google Sheets based on criteria?

Can Google Sheets pull data from another sheet?