Combining {Array} formula and IF and Indirect

Timothy Sayer 0
Viewed 21

I have lists on data in several sheets within one workbook, I would like to use an Array type of formula to bring together one long list of all the data in row A. But I would only like to bring across some of the sheets data, which I have said "yes" to. As the data in sensitive I have setup a dummy spreadsheet which emulates my setup. I seem to be able to get one sheet on data to array, but I can't get the others. =if(Sheet1!$B2>0,{indirect(Sheet1!$A2&"!$A2:$A")}) Can anyone help me? Sheet2 is where I want to import the data into. https://docs.google.com/spreadsheets/d/1Y1OMfEdpMIcptnkuHRD2dNQNI5iUeidkuKnRu08Y_jA/edit#gid=703073676

google-sheets
1 Answers

today you can use:

=QUERY({
 IF(Sheet1!B2="y", INDIRECT(Sheet1!A2&"!A2:A"), );
 IF(Sheet1!B3="y", INDIRECT(Sheet1!A3&"!A2:A"), );
 IF(Sheet1!B4="y", INDIRECT(Sheet1!A4&"!A2:A"), );
 IF(Sheet1!B5="y", INDIRECT(Sheet1!A5&"!A2:A"), );
 IF(Sheet1!B6="y", INDIRECT(Sheet1!A6&"!A2:A"), );
 IF(Sheet1!B7="y", INDIRECT(Sheet1!A7&"!A2:A"), );
 IF(Sheet1!B8="y", INDIRECT(Sheet1!A8&"!A2:A"), )}, 
 "where Col1 is not null", )

and from 1st Oct, 2022 you can try:

=QUERY(BYROW(FILTER(Sheet1!A2:A, Sheet1!B2:B="y"), 
 LAMBDA(x, INDIRECT(x&"!A2:A"))), "where Col1 is not null", )
player00
Related