In Google Sheets, there’s a specific function called IMPORTRANGE which you can refer to another sheet (workbook). To use this function, you need two things:
IMPORTRANGE("Sheet's URL","Address")- Sheet’s URL – URL of the Second Google Sheet
- Address – Range Address with the Sheets Name
In IMPORTRANGE, there are two arguments that you need to define with these two values. In the below example, you have Google Sheet Workbooks.
Now you need to create a reference of the second spreadsheet from the first one.
- First, go to the second workbook, and copy its URL from the address bar before the last forward slash.
- Return to the first spreadsheet and paste this URL into a cell. Of course, you can also enter it directly into the function, but I’d suggest you enter it in a cell and then refer to that cell in the function.
- Next, insert the sheet’s name and the cell or range address you want to refer to in the second spreadsheet. This reference needs to like this sheet_name, exclamation, and the address of the range.
- After that, enter the IMPORTRANGE in a cell. And then refer to cell B1 in the first argument and B2 in the second.
- In the end, hit enter to get the result.
When you hit enter, it takes a second and returns the value you have in the cell A1 of Sheet1 from the workbook whose URL you specified. And if you want to add, specify the URL and the Address directly into the function.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/19bpfk7U9U4evaR11rsug8WWgY5ZIhngijrMjfAfMukw","Sheet1!A1")Now let’s say you want to calculate the sum values in the second spreadsheet in the range A1:A10. You can write a formula like the following:
=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/19bpfk7U9U4evaR11rsug8WWgY5ZIhngijrMjfAfMukw","Sheet1!A1:A10"))
In this formula, IMPORTRANGE gets the values from the range A1:A10, and then SUM calculates the sum for those values.