3

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

Improve this question
5
  • 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 ♦
    Feb 17, 2019 at 19:26
  • The formula is on ZDP08 sheet, cell A8. I'll change to comment mode.
    – Paulo Ruzanovsky
    Feb 17, 2019 at 19:37
  • 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 ♦
    Feb 17, 2019 at 19:56
  • Edited. Sorry, the formula is on ZDP08, A2.
    – Paulo Ruzanovsky
    Feb 17, 2019 at 20:03
  • 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 ♦
    Feb 17, 2019 at 20:08

2 Answers 2

Reset to default
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; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""})}

Improve this answer
3
  • 1
    Thank you again mate, you solved my issue.
    – Paulo Ruzanovsky
    Feb 17, 2019 at 20:39
  • Do you have a good resource to learn about queries or google sheets in general?
    – Paulo Ruzanovsky
    Feb 17, 2019 at 20:47
  • 2
    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
    – user0
    Feb 17, 2019 at 20:52
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.

Improve this answer
2
  • Thank you Rubén, IFERROR fixes the formula indeed.
    – Paulo Ruzanovsky
    Feb 17, 2019 at 20:39
  • @PauloRuzanovsky You're welcome.
    – Rubén ♦
    Feb 17, 2019 at 21:00

Your Answer

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

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