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










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
    7 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
    6 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
    6 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










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
    7 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
    6 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
    6 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










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






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 6 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 7 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
    7 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
    6 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
    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
    7 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
    6 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
    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
7 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
7 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
6 hours ago





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

– Paulo Ruzanovsky
6 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
6 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
6 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
    5 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 6 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
    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
    5 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 6 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
    5 hours ago



















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

    – Paulo Ruzanovsky
    5 hours ago











  • @PauloRuzanovsky You're welcome.

    – Rubén
    5 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
5 hours ago





@PauloRuzanovsky You're welcome.

– Rubén
5 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

Magento 2 controller redirect on button click in phtml file

Polycentropodidae