How to select selected and next row after validation of formula in Google sheet?

1
votes

I have some problems with the formula in Google sheet. I trying to pull some data from multiple sheets using query, but I need to also take the next row of that data, is it possible to do it using formula?

sheet link: https://docs.google.com/spreadsheets/d/1rxhSECdfGiKWb5AoqE26vNk56nvDf-b8bZzpiKA39lk/edit?usp=sharing

2
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Thank you for including your spreadsheet. However, your question does not describe what you are trying to achieve, the specific formula that you are having trouble with, the outcome that you are trying to achieve. Please edit your question to explain these things. – Tedinoz
Please take some time to phrase your question in a specific-enough manner so that it's understandable to others. Also, please read Why can someone help me is not an actual question and How to ask a good question – Anton Dementiev

2 Answers

1
votes

try:

={'01'!A1:M1; ARRAYFORMULA(QUERY({
 '01'!A2:J, IF('01'!K2:K<>"", '01'!K2:K, '01'!K1:K), '01'!L2:M; 
 '02'!A2:J, IF('02'!K2:K<>"", '02'!K2:K, '02'!K1:K), '02'!L2:M}, 
 "where Col11='CS'", 0))}

0

0
votes

1st:

=query(arrayformula(
       {'01'!A1:M1;
        '01'!A2:J, 
        if('01'!K2:K="", '01'!K1:K,'01'!K2:K),
        '01'!L2:L,
        if('01'!C2:C<>"",1,1);
        '02'!A1:M1;
        '02'!A2:J,
        if('02'!K2:K="",'02'!K1:K,'02'!K2:K),
        '02'!L2:L,
        if('02'!C2:C<>"",2,2)}),
        "Select * where Col11='CS'")

2nd:

=query({'01'!A1:N;
       arrayformula(
                     {
                       '01'!A2:L,
                       if('01'!C2:C="",0,1),
                       if('01'!K2:K="",'01'!K1:K,'01'!K2:K);
                       '02'!A2:L,
                       if('02'!C2:C="",0,2),
                       if('02'!K2:K="",'02'!K1:K,'02'!K2:K)
                     }
                   )
        },
          "Select * where Col14='CS'"
      )