I would recommend an { array expression }, like this:
={ Sheet1!A2:G }
This is more or less the same as
=arrayformula(Sheet1!A2:G)
...but I prefer the {} syntax because it allows you to specify non-adjacent columns. For example, you can skip columns D and F like this:
={ Sheet1!A2:C, Sheet1!E2:E, Sheet1!G2:G }
In spreadsheets where the locale uses the comma as decimal mark instead of the period, use a backslash \ instead of comma as horizontal separator.
To skip rows, use the semicolon ; as vertical separator. For example, you can skip rows 2:9 like this:
={ Sheet1!A1:G1; Sheet1!A10:G }
The open-ended range reference A10:G means "columns A to G starting in row 10 and extending all the way to the bottom of the sheet."
You can also leave out the row number to get an open-ended range reference like A:G which means "columns A to G from the very top to the bottom of the sheet." This reference will behave the same as A1:G in almost all situations. I have made it a habit to always include the start row in the reference because that way the formula will automatically adjust in the event a row is inserted above row 1.
When the source sheet is a form responses sheet, another tactic is needed. Form responses are always inserted in newly created rows that cannot be referenced directly in advance.
To avoid the range reference from adjusting when you dynamically copy form responses to another sheet, start the copy from row 1, like this:
={ 'Form Responses 1'!A1:A }
Alternatively, use an array formula, like this:
=arrayformula(
if(
row('Form Responses 1'!A1:A) = 1,
"Enter column header here",
'Form Responses 1'!A1:A
)
)
An even better way to deal with form responses is to aggregate the data directly to whatever reports you need with the query() function.
Answer from doubleunary on Stack OverflowI would recommend an { array expression }, like this:
={ Sheet1!A2:G }
This is more or less the same as
=arrayformula(Sheet1!A2:G)
...but I prefer the {} syntax because it allows you to specify non-adjacent columns. For example, you can skip columns D and F like this:
={ Sheet1!A2:C, Sheet1!E2:E, Sheet1!G2:G }
In spreadsheets where the locale uses the comma as decimal mark instead of the period, use a backslash \ instead of comma as horizontal separator.
To skip rows, use the semicolon ; as vertical separator. For example, you can skip rows 2:9 like this:
={ Sheet1!A1:G1; Sheet1!A10:G }
The open-ended range reference A10:G means "columns A to G starting in row 10 and extending all the way to the bottom of the sheet."
You can also leave out the row number to get an open-ended range reference like A:G which means "columns A to G from the very top to the bottom of the sheet." This reference will behave the same as A1:G in almost all situations. I have made it a habit to always include the start row in the reference because that way the formula will automatically adjust in the event a row is inserted above row 1.
When the source sheet is a form responses sheet, another tactic is needed. Form responses are always inserted in newly created rows that cannot be referenced directly in advance.
To avoid the range reference from adjusting when you dynamically copy form responses to another sheet, start the copy from row 1, like this:
={ 'Form Responses 1'!A1:A }
Alternatively, use an array formula, like this:
=arrayformula(
if(
row('Form Responses 1'!A1:A) = 1,
"Enter column header here",
'Form Responses 1'!A1:A
)
)
An even better way to deal with form responses is to aggregate the data directly to whatever reports you need with the query() function.
It's either:
ArrayFormula(Sheet1!A2:G500)for the 499 lines, orArrayFormula(Sheet!A2:G)if you wanto sync everything from line 2 down
Dynamically Reference Another Sheet With ArrayFormula - Google Docs Editors Community
How to mass reference another sheet on an array of cells?
spreadsheet - Returning ArrayFormula from multiple sheets, in the same cell - Stack Overflow
formulas - Getting data from another Google sheet that isn't in the same order - Web Applications Stack Exchange
I'm trying to reference a bunch of cells in a different sheet, but because they are separated by other cells I have to use an array range. I'd prefer not to have the sheet name in front of every cell reference and just reference the array to the sheet. Is this possible?
e.g. In Sheet2 I want to reference cells - C:3, E:3, G:3, I:3, K:3, M:3, O:3, Q:3 and S:3 - in Sheet1.
Instead of writing {'Sheet1'!C:3,'Sheet1'!E:3,'Sheet1'!G:3...etc.}, I'd like to somehow reference the entire array to Sheet1, i.e. 'Sheet1'!{C:3,E:3,G:3,I:3,K:3,M:3,O:3,Q:3,S:3}. This doesn't actually work.
I can't seem to do it, is it even possible or do I have to accept my fate and write the sheet reference for every cell reference?
I am trying to use an array formula to show the contents from A2:A in a sheet named 'Performance Fitness' and repeat it infinitely in B6:B skipping every 6th cell using the below formula but it seems to only return and repeatedly show the value from A2 rather than all the contents in column A of the origin sheet. Where am I going wrong?
=ARRAYFORMULA(IF(MOD(ROW(B6:B)-ROW(B6),6)=5,"",IFERROR(INDEX('Performance Fitness'!A2:A,ROW(B6:B)-ROW(B6)+1-QUOTIENT(ROW(B6:B)-ROW(B6),6)))))
https://docs.google.com/spreadsheets/d/1CVnS-bdhlEMLA6No6i0dVuqKBzhw4NJayo79EVTjpo0/edit?usp=sharing
I currently have this formula for combining some sheets based on links to the sheets in another cell:
=ARRAYFORMULA({
Importrange('Sheet2'!$G2, "Sheet1!B2:AI");
Importrange('Sheet2'!$G3, "Sheet1!B2:AI")
})Which works well, but I'm wondering if there's a way to make it work with a range instead of a line for each cell? Something like:
=ARRAYFORMULA({
Importrange('Domain Analysis'!$G2:G100, "Sheet1!B2:AI")
})Thanks in advance.