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.
Both parameters of importrange are strings. Strings can include values from other cells, by way of concatenation. For example,
=importrange("Spreadsheet A Key", A1 & "!B1")
means that the name of the sheet to be imported will be taken from cell A1 of the current sheet.
The character & means concatenation: "Names" & "!B1" is "Names!B1".
Best Answer
May be an old question but its still being looked up - so here is the workaround:
Importrange as we all know will only import raw un formatted data into a new googlesheet. As a workaround to avoid the hassle of redoing all the conditional formatting rules on the new import data sheet, do the following: - on the original sheet which you plan to import data from- right click the sheet tab n click 'copy to'
when the browse file window pops up select the sheet you plan to use the importrange formula on.
once you get the successful copy message - open that sheet - press ctl A and press the delete key
then in cell A1 type your importrange formula - your data will now display with all conditional formatting rules that have been set in the original sheet which you are importing from.
check out my post on "importrange while preserving conditional formatting rules" https://productforums.google.com/forum/#!topic/docs/-KnnBPq4d24