0

I have this formula:

=ArrayFormula({query(query({A3:D,value(B3:B)}, 
                     "select Col1, sum(Col5), count(Col4), sum(Col3) 
                      where Col1<>'' group by Col1 format sum(Col5) 'h:mm:ss'"),
               "select* offset 1",0), 

 vlookup(query(query(query(A4:D, 
                     "select D, A, sum(C) group by A, D"), 
               "select max(Col3), Col2 group by Col2 label max(Col3)''"), 
         "select Col1")&unique(sort(filter(A4:A, len(A4:A)))), 

 {query(query(A4:D, "select sum(C), A, D  group by A, D"), 
        "select Col1",0)&

 query(query(A4:D, 
       "select sum(C), A, D group by A, D"), 
 "select Col2",0),

 query(query(A4:D, 
       "select sum(C), A, D  group by A, D"), 
 "select  Col3",0)}, 2, 0)})

I got it from a response to another question here in Web Applications, this is an example, I am trying to apply it to my real sheet.

The thing is that this formula is going to be in a different sheet (in one sheet the raw data, in another sheet the analytics using this formula), so how can I call Col1, Col2, etc from another sheet?

Can I mix in the same sentence columns from different sheets?

Example: "Select sheet1!Col1, sum(Col5), count(sheet1!Col4)"

1

Instead of just using the ranges, you will have to add the sheet name..

e.g:

=ArrayFormula({query(query({'SHEETNAME'!A3:D,value('SHEETNAME'!B3:B)}....ETC
  • 1
    I Hopea) you only reference one other sheet (the sheet with the 'source data'). Only add in the sheetname where you see ranges, NOT in the select clause of the query like in the example you gave. – JPV Jan 16 '15 at 18:55
  • Is this wrong? =ArrayFormula({query(query({'SHEETNAME1'!A3:D,value('SHEETNAME2'!B3:B)}....ETC – elunicotomas Jan 16 '15 at 19:06
  • @elunicotomas: looks ok – JPV Jan 16 '15 at 19:30
  • sorry, what I was actually asking is to reference that formula in "select Col1, sum (Col5) ... ETC", after the "query" part, is it clear?? – elunicotomas Jan 19 '15 at 17:04
0

Select sheet1!Col1, sum(Col5), count(sheet1!Col4)"

This cannot be done. You can only reference them outside the query formula. Not inside.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.