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

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. Feb 17, 2019 at 19:26
  • The formula is on ZDP08 sheet, cell A8. I'll change to comment mode. 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. Feb 17, 2019 at 19:56
  • Edited. Sorry, the formula is on ZDP08, A2. 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) Feb 17, 2019 at 20:08

2 Answers 2

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

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

2

Your Answer

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

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