CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
dion dion
dion dion

Reputation: 113

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

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

Upvotes: 1

Views: 449

Answers (2)

user11982798
user11982798

Reputation: 1908

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'"
      )

Upvotes: 0

player0
player0

Reputation: 1

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

Upvotes: 1

Related Questions