google sheetsgoogle-sheets-arrayformula
I'm trying to fill a formula down a column using ARRAYFORMULA. However, there are certain cells in this column which I would like to overwrite, but all the other cells should use the ARRAYFORMULA.
Is it possible to use ARRAYFORMULA with overrides? Whenever I try to override a cell's data currently in the column, I get the following error:
Array result was not expanded because it would overwrite data in (some cell).
Use the following formula to accomplish that.
=QUERY(
'GR Live Campaign Tracker'!J:L, // data range
"SELECT J, COUNT(L) WHERE L IS NOT NULL GROUP BY J", // select
0 // headers
)
Added the formula into your example file.
No, it's not possible to "skip columns".
Instead you could use two query functions, one to be used to get the columns A - C, the other to get the columns F and following.
First formula (A1):
=QUERY(
IMPORTRANGE("URL","Sheet Name!A2:P"),
"SELECT Col1,Col2,Col3
WHERE Col11 CONTAINS 'West'",
1
)
Second formula (F1):
=QUERY(
IMPORTRANGE("URL","Sheet Name!A2:P"),
"SELECT Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15
WHERE Col11 CONTAINS 'West'",
1
)
REMARKS:
Note that instead of Sheet Name!A2:P1000 was used Sheet Name!A2:P as this take the whole columns instead of only 1000 rows.
Best Answer
this error indicates that some cell isn't empty therefore array won't be executed on a given range/scale. your options are:
=ARRAYFORMULA({C1;C3:C7;C9:C})