I have this spreadsheet as an example to illustrate the problem.
I have two queries referencing different sheets on the same spreadsheet.
=QUERY(Transfers!A1:H, "select F where E = 'Mild Ale (24) '") =QUERY(stocktake!A1:J, "select I where H = 'TOTAL:'") One is displaying an error, the other is not. I'm unable to determine what is causing the error as both queries are sufficiently similar enough.
Much appreciated.
From Query Language Syntax:
All values of a column will have a data type that matches the column type, or a null value.
Your problem is that Column H has mixed types, number and string.
LOOKUP functions instead of a Query.Seems to be because column H in stocktake has been formatted as automatic and the query is objecting to selecting a text value out of it.
One solution may be to apply numeric codes for TOTAL:, WASTAGE:, INCOME: and DISCREPANCY:. Another might be put those descriptions at the foot of ColumnB and adjust the query accordingly.
Another possibility would be to format ColumnH as Plain text.
In addition to previous post, you can convert everything to text in the query. Something like this should work:
=ArrayFormula(QUERY(stocktake!A1:J&"", "select Col9 where Col8 = 'TOTAL:'")) If you want to output to be format as number, then adding +0 should do the trick:
=ArrayFormula(QUERY(stocktake!A1:J&"", "select Col9 where Col8 = 'TOTAL:'")+0) NOTE: for the current situation an alternative (an probably easier) way, would be to use vlookup.
=vlookup("TOTAL:", stocktake!H1:I30, 2, 0) Hope that helps ?
