Query returning #VALUE error when the column has no values












1















the below query returns #VALUE error when the column H is completely empty:



=QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 

 WHERE H IS NOT NULL

 LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''")


How do I make it only ignore this query and not give the value error? Because this query is only for one month, I have code for the other 11 months and wanted to simply omit the months without values. The current fix I found is to put a 0 on each column, to make the query run.



Find below the copy of the spreadsheet, with the full query. The formula is on the ZDP08 sheet, cell A2:
spreadsheet cc






google-sheets formulas google-sheets-query google-sheets-arrayformula







share|improve this question









New contributor




Paulo Ruzanovsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





















  • Where on your spreadsheet is the formula referred in the question? I think that linked spreadsheets should be shared as view or comment, not edit as anyone trying to help could modify the spreadsheet. One alternative is to assign a name to certain version and mention that name in the question.

    – Rubén
    6 hours ago













  • The formula is on ZDP08 sheet, cell A8. I'll change to comment mode.

    – Paulo Ruzanovsky
    6 hours ago











  • A8 doesn't has a formula, and there isn't a #VALUE error on ZDP08 sheet. Please edit the question to add to it all the relevant details.

    – Rubén
    6 hours ago











  • Edited. Sorry, the formula is on ZDP08, A2.

    – Paulo Ruzanovsky
    5 hours ago











  • 1. The formula on A2 isn't the same as the one show on the question. Please remove the equal sign and mention that the query is part of an array. 2. The cell has a red triangle that on mouse over shows an error description. Please add that error description textually to the question (my account is in Spanish)

    – Rubén
    5 hours ago
















1















the below query returns #VALUE error when the column H is completely empty:



=QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 

 WHERE H IS NOT NULL

 LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''")


How do I make it only ignore this query and not give the value error? Because this query is only for one month, I have code for the other 11 months and wanted to simply omit the months without values. The current fix I found is to put a 0 on each column, to make the query run.



Find below the copy of the spreadsheet, with the full query. The formula is on the ZDP08 sheet, cell A2:
spreadsheet cc






google-sheets formulas google-sheets-query google-sheets-arrayformula







share|improve this question









New contributor




Paulo Ruzanovsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





















  • Where on your spreadsheet is the formula referred in the question? I think that linked spreadsheets should be shared as view or comment, not edit as anyone trying to help could modify the spreadsheet. One alternative is to assign a name to certain version and mention that name in the question.

    – Rubén
    6 hours ago













  • The formula is on ZDP08 sheet, cell A8. I'll change to comment mode.

    – Paulo Ruzanovsky
    6 hours ago











  • A8 doesn't has a formula, and there isn't a #VALUE error on ZDP08 sheet. Please edit the question to add to it all the relevant details.

    – Rubén
    6 hours ago











  • Edited. Sorry, the formula is on ZDP08, A2.

    – Paulo Ruzanovsky
    5 hours ago











  • 1. The formula on A2 isn't the same as the one show on the question. Please remove the equal sign and mention that the query is part of an array. 2. The cell has a red triangle that on mouse over shows an error description. Please add that error description textually to the question (my account is in Spanish)

    – Rubén
    5 hours ago














1












1








1








the below query returns #VALUE error when the column H is completely empty:



=QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 

 WHERE H IS NOT NULL

 LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''")


How do I make it only ignore this query and not give the value error? Because this query is only for one month, I have code for the other 11 months and wanted to simply omit the months without values. The current fix I found is to put a 0 on each column, to make the query run.



Find below the copy of the spreadsheet, with the full query. The formula is on the ZDP08 sheet, cell A2:
spreadsheet cc






google-sheets formulas google-sheets-query google-sheets-arrayformula







share|improve this question









New contributor




Paulo Ruzanovsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












the below query returns #VALUE error when the column H is completely empty:



=QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 

 WHERE H IS NOT NULL

 LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''")


How do I make it only ignore this query and not give the value error? Because this query is only for one month, I have code for the other 11 months and wanted to simply omit the months without values. The current fix I found is to put a 0 on each column, to make the query run.



Find below the copy of the spreadsheet, with the full query. The formula is on the ZDP08 sheet, cell A2:
spreadsheet cc






google-sheets formulas google-sheets-query google-sheets-arrayformula




google-sheets formulas google-sheets-query google-sheets-arrayformula






share|improve this question









New contributor




Paulo Ruzanovsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Paulo Ruzanovsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 5 hours ago









MARK MY ANSWER

7,42751230




7,42751230






New contributor




Paulo Ruzanovsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 6 hours ago









Paulo RuzanovskyPaulo Ruzanovsky

154




154




New contributor




Paulo Ruzanovsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Paulo Ruzanovsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Paulo Ruzanovsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.













  • Where on your spreadsheet is the formula referred in the question? I think that linked spreadsheets should be shared as view or comment, not edit as anyone trying to help could modify the spreadsheet. One alternative is to assign a name to certain version and mention that name in the question.

    – Rubén
    6 hours ago













  • The formula is on ZDP08 sheet, cell A8. I'll change to comment mode.

    – Paulo Ruzanovsky
    6 hours ago











  • A8 doesn't has a formula, and there isn't a #VALUE error on ZDP08 sheet. Please edit the question to add to it all the relevant details.

    – Rubén
    6 hours ago











  • Edited. Sorry, the formula is on ZDP08, A2.

    – Paulo Ruzanovsky
    5 hours ago











  • 1. The formula on A2 isn't the same as the one show on the question. Please remove the equal sign and mention that the query is part of an array. 2. The cell has a red triangle that on mouse over shows an error description. Please add that error description textually to the question (my account is in Spanish)

    – Rubén
    5 hours ago



















  • Where on your spreadsheet is the formula referred in the question? I think that linked spreadsheets should be shared as view or comment, not edit as anyone trying to help could modify the spreadsheet. One alternative is to assign a name to certain version and mention that name in the question.

    – Rubén
    6 hours ago













  • The formula is on ZDP08 sheet, cell A8. I'll change to comment mode.

    – Paulo Ruzanovsky
    6 hours ago











  • A8 doesn't has a formula, and there isn't a #VALUE error on ZDP08 sheet. Please edit the question to add to it all the relevant details.

    – Rubén
    6 hours ago











  • Edited. Sorry, the formula is on ZDP08, A2.

    – Paulo Ruzanovsky
    5 hours ago











  • 1. The formula on A2 isn't the same as the one show on the question. Please remove the equal sign and mention that the query is part of an array. 2. The cell has a red triangle that on mouse over shows an error description. Please add that error description textually to the question (my account is in Spanish)

    – Rubén
    5 hours ago

















Where on your spreadsheet is the formula referred in the question? I think that linked spreadsheets should be shared as view or comment, not edit as anyone trying to help could modify the spreadsheet. One alternative is to assign a name to certain version and mention that name in the question.

– Rubén
6 hours ago







Where on your spreadsheet is the formula referred in the question? I think that linked spreadsheets should be shared as view or comment, not edit as anyone trying to help could modify the spreadsheet. One alternative is to assign a name to certain version and mention that name in the question.

– Rubén
6 hours ago















The formula is on ZDP08 sheet, cell A8. I'll change to comment mode.

– Paulo Ruzanovsky
6 hours ago





The formula is on ZDP08 sheet, cell A8. I'll change to comment mode.

– Paulo Ruzanovsky
6 hours ago













A8 doesn't has a formula, and there isn't a #VALUE error on ZDP08 sheet. Please edit the question to add to it all the relevant details.

– Rubén
6 hours ago





A8 doesn't has a formula, and there isn't a #VALUE error on ZDP08 sheet. Please edit the question to add to it all the relevant details.

– Rubén
6 hours ago













Edited. Sorry, the formula is on ZDP08, A2.

– Paulo Ruzanovsky
5 hours ago





Edited. Sorry, the formula is on ZDP08, A2.

– Paulo Ruzanovsky
5 hours ago













1. The formula on A2 isn't the same as the one show on the question. Please remove the equal sign and mention that the query is part of an array. 2. The cell has a red triangle that on mouse over shows an error description. Please add that error description textually to the question (my account is in Spanish)

– Rubén
5 hours ago





1. The formula on A2 isn't the same as the one show on the question. Please remove the equal sign and mention that the query is part of an array. 2. The cell has a red triangle that on mouse over shows an error description. Please add that error description textually to the question (my account is in Spanish)

– Rubén
5 hours ago










2 Answers
2






active

oldest

votes


















1















  • wrap it in IFERROR and add fake columns for false part


IFERROR(your-query-here; {""""""""""""""""""""""""""""""""""})



full formula in ZDP08!A2 cell:



={

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!B5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!B5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!C5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!C5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!D5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!D5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!E5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!E5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!F5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!F5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!G5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!G5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!H5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!H5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!I5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!I5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!J5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!J5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!K5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!K5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!L5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!L5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!M5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!M5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!N5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!N5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!O5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!O5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!P5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!P5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!Q5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!Q5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!R5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!R5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!S5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!S5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!T5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!T5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!U5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!U5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!V5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!V5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!W5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!W5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!X5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!X5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""})}







share|improve this answer



















  • 1





    Thank you again mate, you solved my issue.

    – Paulo Ruzanovsky
    5 hours ago











  • Do you have a good resource to learn about queries or google sheets in general?

    – Paulo Ruzanovsky
    5 hours ago






  • 1





    not rly... mostly I have no clue how to solve the question, so I open a sheet and test out stuff until I crack it. thats why every answer comes with screenshot as proof xD

    – MARK MY ANSWER
    5 hours ago





















1















How do I make it only ignore this query and not give the value error?




Use IFERROR




Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.







share|improve this answer
























  • Thank you Rubén, IFERROR fixes the formula indeed.

    – Paulo Ruzanovsky
    5 hours ago











  • @PauloRuzanovsky You're welcome.

    – Rubén
    4 hours ago











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "34"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
noCode: true, onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});






Paulo Ruzanovsky is a new contributor. Be nice, and check out our Code of Conduct.










draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fwebapps.stackexchange.com%2fquestions%2f125432%2fquery-returning-value-error-when-the-column-has-no-values%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









1















  • wrap it in IFERROR and add fake columns for false part


IFERROR(your-query-here; {""""""""""""""""""""""""""""""""""})



full formula in ZDP08!A2 cell:



={

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!B5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!B5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!C5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!C5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!D5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!D5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!E5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!E5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!F5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!F5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!G5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!G5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!H5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!H5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!I5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!I5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!J5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!J5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!K5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!K5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!L5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!L5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!M5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!M5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!N5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!N5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!O5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!O5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!P5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!P5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!Q5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!Q5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!R5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!R5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!S5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!S5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!T5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!T5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!U5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!U5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!V5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!V5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!W5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!W5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!X5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!X5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""})}







share|improve this answer



















  • 1





    Thank you again mate, you solved my issue.

    – Paulo Ruzanovsky
    5 hours ago











  • Do you have a good resource to learn about queries or google sheets in general?

    – Paulo Ruzanovsky
    5 hours ago






  • 1





    not rly... mostly I have no clue how to solve the question, so I open a sheet and test out stuff until I crack it. thats why every answer comes with screenshot as proof xD

    – MARK MY ANSWER
    5 hours ago


















1















  • wrap it in IFERROR and add fake columns for false part


IFERROR(your-query-here; {""""""""""""""""""""""""""""""""""})



full formula in ZDP08!A2 cell:



={

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!B5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!B5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!C5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!C5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!D5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!D5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!E5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!E5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!F5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!F5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!G5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!G5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!H5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!H5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!I5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!I5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!J5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!J5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!K5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!K5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!L5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!L5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!M5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!M5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!N5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!N5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!O5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!O5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!P5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!P5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!Q5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!Q5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!R5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!R5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!S5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!S5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!T5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!T5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!U5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!U5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!V5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!V5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!W5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!W5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!X5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!X5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""})}







share|improve this answer



















  • 1





    Thank you again mate, you solved my issue.

    – Paulo Ruzanovsky
    5 hours ago











  • Do you have a good resource to learn about queries or google sheets in general?

    – Paulo Ruzanovsky
    5 hours ago






  • 1





    not rly... mostly I have no clue how to solve the question, so I open a sheet and test out stuff until I crack it. thats why every answer comes with screenshot as proof xD

    – MARK MY ANSWER
    5 hours ago
















1












1








1








  • wrap it in IFERROR and add fake columns for false part


IFERROR(your-query-here; {""""""""""""""""""""""""""""""""""})



full formula in ZDP08!A2 cell:



={

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!B5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!B5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!C5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!C5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!D5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!D5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!E5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!E5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!F5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!F5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!G5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!G5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!H5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!H5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!I5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!I5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!J5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!J5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!K5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!K5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!L5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!L5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!M5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!M5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!N5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!N5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!O5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!O5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!P5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!P5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!Q5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!Q5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!R5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!R5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!S5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!S5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!T5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!T5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!U5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!U5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!V5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!V5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!W5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!W5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!X5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!X5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""})}







share|improve this answer














  • wrap it in IFERROR and add fake columns for false part


IFERROR(your-query-here; {""""""""""""""""""""""""""""""""""})



full formula in ZDP08!A2 cell:



={

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!B5;","; ".")&" 

WHERE H IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!B5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!C5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!C5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!D5;","; ".")&" 

WHERE I IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!D5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!E5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!E5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!F5;","; ".")&" 

WHERE J IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!F5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!G5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!G5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!H5;","; ".")&" 

WHERE K IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!H5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!I5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!I5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!J5;","; ".")&" 

WHERE L IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!J5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!K5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!K5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!L5;","; ".")&" 

WHERE M IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!L5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!M5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!M5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!N5;","; ".")&" 

WHERE N IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!N5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!O5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!O5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!P5;","; ".")&" 

WHERE O IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!P5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!Q5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!Q5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!R5;","; ".")&" 

WHERE P IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!R5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!S5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!S5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!T5;","; ".")&" 

WHERE Q IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!T5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!U5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!U5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!V5;","; ".")&" 

WHERE R IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!V5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!W5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!W5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""});

IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!X5;","; ".")&" 

WHERE S IS NOT NULL

LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!X5; ","; ".")&" ''");{""""""""""""""""""""""""""""""""""})}








share|improve this answer












share|improve this answer



share|improve this answer










answered 5 hours ago









MARK MY ANSWERMARK MY ANSWER

7,42751230




7,42751230








  • 1





    Thank you again mate, you solved my issue.

    – Paulo Ruzanovsky
    5 hours ago











  • Do you have a good resource to learn about queries or google sheets in general?

    – Paulo Ruzanovsky
    5 hours ago






  • 1





    not rly... mostly I have no clue how to solve the question, so I open a sheet and test out stuff until I crack it. thats why every answer comes with screenshot as proof xD

    – MARK MY ANSWER
    5 hours ago
















  • 1





    Thank you again mate, you solved my issue.

    – Paulo Ruzanovsky
    5 hours ago











  • Do you have a good resource to learn about queries or google sheets in general?

    – Paulo Ruzanovsky
    5 hours ago






  • 1





    not rly... mostly I have no clue how to solve the question, so I open a sheet and test out stuff until I crack it. thats why every answer comes with screenshot as proof xD

    – MARK MY ANSWER
    5 hours ago










1




1





Thank you again mate, you solved my issue.

– Paulo Ruzanovsky
5 hours ago





Thank you again mate, you solved my issue.

– Paulo Ruzanovsky
5 hours ago













Do you have a good resource to learn about queries or google sheets in general?

– Paulo Ruzanovsky
5 hours ago





Do you have a good resource to learn about queries or google sheets in general?

– Paulo Ruzanovsky
5 hours ago




1




1





not rly... mostly I have no clue how to solve the question, so I open a sheet and test out stuff until I crack it. thats why every answer comes with screenshot as proof xD

– MARK MY ANSWER
5 hours ago







not rly... mostly I have no clue how to solve the question, so I open a sheet and test out stuff until I crack it. thats why every answer comes with screenshot as proof xD

– MARK MY ANSWER
5 hours ago















1















How do I make it only ignore this query and not give the value error?




Use IFERROR




Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.







share|improve this answer
























  • Thank you Rubén, IFERROR fixes the formula indeed.

    – Paulo Ruzanovsky
    5 hours ago











  • @PauloRuzanovsky You're welcome.

    – Rubén
    4 hours ago
















1















How do I make it only ignore this query and not give the value error?




Use IFERROR




Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.







share|improve this answer
























  • Thank you Rubén, IFERROR fixes the formula indeed.

    – Paulo Ruzanovsky
    5 hours ago











  • @PauloRuzanovsky You're welcome.

    – Rubén
    4 hours ago














1












1








1








How do I make it only ignore this query and not give the value error?




Use IFERROR




Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.







share|improve this answer














How do I make it only ignore this query and not give the value error?




Use IFERROR




Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.








share|improve this answer












share|improve this answer



share|improve this answer










answered 5 hours ago









RubénRubén

27.8k637172




27.8k637172













  • Thank you Rubén, IFERROR fixes the formula indeed.

    – Paulo Ruzanovsky
    5 hours ago











  • @PauloRuzanovsky You're welcome.

    – Rubén
    4 hours ago



















  • Thank you Rubén, IFERROR fixes the formula indeed.

    – Paulo Ruzanovsky
    5 hours ago











  • @PauloRuzanovsky You're welcome.

    – Rubén
    4 hours ago

















Thank you Rubén, IFERROR fixes the formula indeed.

– Paulo Ruzanovsky
5 hours ago





Thank you Rubén, IFERROR fixes the formula indeed.

– Paulo Ruzanovsky
5 hours ago













@PauloRuzanovsky You're welcome.

– Rubén
4 hours ago





@PauloRuzanovsky You're welcome.

– Rubén
4 hours ago










Paulo Ruzanovsky is a new contributor. Be nice, and check out our Code of Conduct.










draft saved

draft discarded


















Paulo Ruzanovsky is a new contributor. Be nice, and check out our Code of Conduct.













Paulo Ruzanovsky is a new contributor. Be nice, and check out our Code of Conduct.












Paulo Ruzanovsky is a new contributor. Be nice, and check out our Code of Conduct.
















Thanks for contributing an answer to Web Applications Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fwebapps.stackexchange.com%2fquestions%2f125432%2fquery-returning-value-error-when-the-column-has-no-values%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Fluorita

Image
Fluorita Fluorita (blau) amb pirita (daurat). Fórmula química CaF 2 Epònim fluor Localitat tipus Jáchymov Classificació Categoria Halurs Nickel-Strunz 10a ed. 3.AB.25 Nickel-Strunz 9a ed. 3.AB.25 Nickel-Strunz 8a ed. III/A.08 Dana 9.2.1.1 Heys 8.4.7 Propietats Sistema cristal·lí Cúbic Hàbit cristal·lí Sol presentar cubs; menys freqüentment dodecaedres. De vegades hexaoctaedres i tetrahexaedres. Per la combinació d'aquestes formes, les arestes dels cubs són sovint modificades. De vegades els cristalls poden presentar diferències de creixement entre les cares. Sovint s'observen cristalls compostos per sobrecreixement. Pot ser massiva, compacta, terrosa, columnar (poc freqüent), globular en agregats o botroïdal. Estructura cristal·lina a = 5.4626Å Simetria Classe (H-M): m 3 m (4/ m 3 2/ m ) - Hexoctaèdric; Grup espacial: Fm 3 m Color Blanc, groc, verd, violeta, vermell, rosa, blau o negre Mac...
Read more

Hulsita

Image
Hulsita Hulsita de la localitat tipus Fórmula química Fe 2+ 2 Fe 3+ O 2 BO 3 Epònim Alfred Hulse Brooks Localitat tipus mont Brooks, península de Seward, Nome Borough, Alaska, Estats Units Classificació Categoria borats Nickel-Strunz 10a ed. 6.AB.45 Nickel-Strunz 9a ed. 6.AB.45 Nickel-Strunz 8a ed. V/G.03 Dana 24.2.3.1 Heys 9.6.4 Propietats Sistema cristal·lí monoclínic Estructura cristal·lina a = 10,68Å; b = 3,09Å; c = 5,43Å; β = 94,15° Color negre Macles en [001]; un membre de la macla es troba girat 120° en relació a l'altre Exfoliació bona en {110} Duresa 3 Lluïssor vítria, submetàl·lica Densitat 4,28 g/cm 3 (mesurada); Més informació Estatus IMA mineral heretat (G) i mineral heretat (G) Any d'aprovació 1908 Referències [1] La hulsita és un mineral de la classe dels borats, que pertany al grup de la pinakiolita. Rep el seu nom en honor del nord-americà ...
Read more

Península de Txukotka

Image
Península de Txukotka Tipus Península Ubicació 66° N, 172° O  /  66°N , 172°O  / 66; -172 Mar mar dels Txuktxis La península de Txukotka a l'extrem oriental de Sibèria La península de Txukotka o península dels Txuktxis (en rus Чуко́тский полуо́стров , Txukotski poluóstrov ) és una península que es troba a l'extrem oriental d'Euràsia. Contingut 1 Descripció 2 Població 3 Vegeu també 4 Bibliografia Descripció L'estret de Bering, entre la península de Txukotka i la península de Seward a Alaska Foto de satèl·lit de la zona amb la península de Txukotka a l'esquerra El seu punt més extrem és el cap Dezhnev a la vora del nucli d'Uelen, un dels poquíssims llocs habitats de la zona. Al nord limita amb el mar dels Txuktxis i amb el mar de Bering al sud i amb l'estret de Bering a l'est. A la costa sud es troben la badia de Sant Lawrence i la badia de Kresta. La penínsu...
Read more