How to reference another sheet in Google Sheets

by

While working in your google spreadsheets a need may arise when you want to reference a cell or a range of cells from another sheet within the same document or another spreadsheet. Let us take a look at how we can do that in google sheets. So, there can be two options,

  • Method 1 – Referencing a cell from another sheet in the same document
  • Method 2 – Referencing a cell or range from another spreadsheet

Method 1 – Referencing a cell from another sheet in the same document

Step 1 – Using cell reference from another sheet

  • Go to https://docs.google.com/spreadsheets/
  • Under ‘Start a new spreadsheet’ section click plus sign to start a blank sheet.
  • In the cell A1 of the Sheet1 type some text.
  • Create Sheet2 by clicking the plus sign next to Sheet1 tab.
  • On the Sheet2 in cell A1 type the formula ‘=Sheet1!A1’ and press enter.
  • The value in cell A1 from Sheet1 will be placed in cell A1 on Sheet2.

Method 2 – Referencing a cell or range from another spreadsheet

Step 1 – Use the IMPORTRANGE formula

  • Let us have two spreadsheets, the first one in which we want to add a reference may be called the host sheet and the other which is to be referenced may be called target sheet.
  • Here we are going to use the IMPORTRANGE formula with the following syntax.

 =IMPORTRANGE(spreadsheet_url, range_string)

  • The first argument is the URL of the document we want to reference.
  • The second argument is the target sheet name with cell range.
  • So enter the above formula in any cell in the host sheet.
  • Use the target sheet’s web URL as the first argument and the sheet name in the target sheet with ! mark followed by the cell number or the cell range as the range_string.
  • If asked please allow the connection between the two spreadsheets.
  • The data from the range_string in the target sheet will be placed in the host sheet cell.

Leave a Comment