Google sheets Arrays to sheets in column A: A via INDIRECT, Arrayformula

Question

https://docs.google.com/spreadsheets/d/1TBi3XFlvIWCyht8kO8CngAxOHEe-wLYmuE361BQodHo/edit#gid=613086152 sheet "Сбор данных", the formula is in cell C1

I use a formula where I add each line manually, can I somehow change it so that the array is processed by itself, by non-empty lines

        =query(UNIQUE({IF(COUNTA(INDIRECT($A1&"!A7:A"))>5;INDIRECT($A1&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A2&"!A7:A"))>5;INDIRECT($A2&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A3&"!A7:A"))>5;INDIRECT($A3&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A4&"!A7:A"))>5;INDIRECT($A4&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A5&"!A7:A"))>5;INDIRECT($A5&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A6&"!A7:A"))>5;INDIRECT($A6&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A7&"!A7:A"))>5;INDIRECT($A7&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A8&"!A7:A"))>5;INDIRECT($A8&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A9&"!A7:A"))>5;INDIRECT($A9&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A10&"!A7:A"))>5;INDIRECT($A10&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A11&"!A7:A"))>5;INDIRECT($A11&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A12&"!A7:A"))>5;INDIRECT($A12&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A13&"!A7:A"))>5;INDIRECT($A13&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A14&"!A7:A"))>5;INDIRECT($A14&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A15&"!A7:A"))>5;INDIRECT($A15&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A16&"!A7:A"))>5;INDIRECT($A16&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A17&"!A7:A"))>5;INDIRECT($A17&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A18&"!A7:A"))>5;INDIRECT($A18&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A19&"!A7:A"))>5;INDIRECT($A19&"!A7:N");INDIRECT($A1&"!A7:N"))

}); "Select Col1,Col3,Col5,Col4,Col6,Col7,Col13,Col14 Where Col1 is not null and not Col1 like 'Методист'";0)

So only the first value is taken and an array is not created:

=ARRAYFORMULA(if(len(A:A)>0;INDIRECT(index($A:A)&"!A7:N");""))

player0 · Accepted Answer · 2021-11-10T12:02:47.733

INDIRECT does not support arrays so...

add this script:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Сбор данных');  // sheet name
var src = sheet.getRange("A1");                                        // cell which holds the formula
var str = src.getValue();  
var cell = sheet.getRange("C2");                                       // cell where I want the results
cell.setFormula(str);              
}

and paste this formula in A1:

=ARRAYFORMULA("=QUERY(UNIQUE({"&TEXTJOIN("; "; 1; "IF(COUNTA("&QUERY(SHEETSNAME(); 
 "where not Col1 matches 'Сводная|Шаблон|Тех Лист|UTM- метки|Сбор данных'"; 0)&"!A7:N)>5; "&QUERY(SHEETSNAME(); 
 "where not Col1 matches 'Сводная|Шаблон|Тех Лист|UTM- метки|Сбор данных'"; 0)&"!A7:N; "&QUERY(SHEETSNAME(); 
 "where not Col1 matches 'Сводная|Шаблон|Тех Лист|UTM- метки|Сбор данных' limit 1"; 0)&"!A7:N)")&
 "}); ""select Col1,Col3,Col5,Col4,Col6,Col7,Col13,Col14 where Col1 is not null and not Col1 like 'Методист'""; 0)")

all will be automated

Google sheets Arrays to sheets in column A: A via INDIRECT, Arrayformula

1 Answers1