How to reference a range of data in another workbook (not importing)?

Question

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.

Answer 1

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.