1

Below is the query that I use, and it works:

=QUERY(Input!A2:I33;
 "SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT(Input!L8;"dd/mm/yyyy")&"', 0.6*H 
  WHERE I='I' 
  LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT(Input!L8;"dd/mm/yyyy")&"' '', 0.6*H ''")

My question is: How can I multiply H by a cell refference? For instance, here's what I tried, but it did not work ("Input!L9" reffers to a cell with the value 0.6):

=QUERY(Input!A2:I33;
 "SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&TEXT(Input!L8;"dd/mm/yyyy")&"', "&Input!L9&"*H 
  WHERE I='I' 
  LABEL '000' '', ' ' '', ' ' '', ' ' '', ' ' '', ' ' '', ' ' '', ' ' '', '"&TEXT(Input!L8;"dd/mm/yyyy")&"' '', "&Input!L9&"*H ''")
0

1 Answer 1

0
  • try H*"&Input!L9&" with label H*"&Input!L9&"''

=QUERY(Input!A2:I33,
 "select '000',A,B,C,D,E,' ',F,'  ','   ','    ',G,'     ','      ','       ','"&TEXT(Input!L8,"dd/mm/yyyy")&"',H*"&Input!L9&" 
  where I='I' 
  label '000''',' ''','  ''','   ''','    ''','     ''','      ''','       ''','"&TEXT(Input!L8,"dd/mm/yyyy")&"''',H*"&Input!L9&"''")

note: EU syntax will need to have SUBSTITUTE(Input!L9; ","; ".") instead of Input!L9

5
  • Thank you for the answer. I tried running your code, but I recieved a #VALUE! error: imgur.com/a/LKBA3ks Feb 16, 2019 at 13:14
  • @PauloRuzanovsky from your picture I can see that you are using different locale... what number is in your L9 cell? 0.6? should be 0,6 in your case
    – user0
    Feb 16, 2019 at 13:35
  • I was aware, the number on L9 was 0,6. I believe the query doesn't understand diferent separators, since I swaped to united states locale and it worked with the dot separator. If you know how to make it work with the comma separator, let me know Here is the copy spreadsheet: docs.google.com/spreadsheets/d/… Feb 16, 2019 at 14:47
  • @PauloRuzanovsky done. check your sheet
    – user0
    Feb 16, 2019 at 19:07
  • Thank you! It definetely solves. Feb 16, 2019 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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