Google-sheets – How to reference a range of data in another workbook (not importing)

google sheets

In Google Sheets, how do I reference a range of data in another workbook (not importing)?

I want to reference a range of data in another workbook. The only information I can find is for ImportRange() which imports all the data in the range, whereas I need to pass the reference to other functions as a range without importing it.

My guess would have been [spreadsheet key]![sheet name]!A1:C20 or something like that. Worth mentioning that the spreadsheet key and sheet name along with the range I want to reference are in cells D27 and E27 respectively within the current workbook.

Best Answer

Spreadsheet functions cannot access the content of other spreadsheets, other than by invoking importrange.

But you can have importrange inside other functions. For example, 

=join(",", importrange("...", "Sheet1!A1:A5"))

returns the content of cells A1:A5 in the other spreadsheet, comma-separated within one cell. Another command often nested is query, which allows you to do a lot of processing of another spreadsheet's data without getting intermediate results in. For example: 

=query(importrange("...",  "Sheet1!A1:B9"), "select Col1, sum(Col2) group by Col1 order by Col1")

returns the totals of the second column split by the values of the first column, and sorts by the first column.

Related Topic