Google Spreadsheet Post #1516
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Feb-05-2014
post by Bee Lini (https://productforums.google.com/forum/#!mydiscussions/docs/kGuhFo4uWgU)
Multiple QUERY and Select Col help with New Google Sheets
Hi Gurus,
I have a formula, probably not a very efficient or short one, but it use to work with the old sheets. While using the new Google sheets I have not been able to get it to work anymore. I'm really not sure where my mistake in formula is.
Here's the error that results:
Error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMNCol3
If anyone could please take a look and let me know what I'm doing wrong I would appreciate it. Here's a link: https://docs.google.com/ spreadsheets/d/ 1nx2suoNLcXmnX19ZE2mCTlsPXEdPJ enYeXyZdF2zyXE/edit#gid= 1448742359
If anyone can figure out a more efficient way to write the formula - I'm all ears!
Thanks
Bee
---
Yogi presented a solution in his blog posts:
I have tried
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Feb-05-2014
post by Bee Lini (https://productforums.google.com/forum/#!mydiscussions/docs/kGuhFo4uWgU)
Multiple QUERY and Select Col help with New Google Sheets
Hi Gurus,
I have a formula, probably not a very efficient or short one, but it use to work with the old sheets. While using the new Google sheets I have not been able to get it to work anymore. I'm really not sure where my mistake in formula is.
=query(query(index('Copy of Master Data'!B:AE),"select "&join(",","Col"&query(index(i f(B1:B29="x",row(B1:B29),))," select* where Col1 is not null "))), "select* where Col1='"&G2&"' and Col3 <= date '"&text(G4,"yyyy-mm-dd")&"' and Col3 >= date '"&text(G3,"yyyy-mm-dd")&"' ")
Here's the error that results:
Error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMNCol3
If anyone could please take a look and let me know what I'm doing wrong I would appreciate it. Here's a link: https://docs.google.com/
If anyone can figure out a more efficient way to write the formula - I'm all ears!
Thanks
Bee
---
Yogi presented a solution in his blog posts:
for use in New Google Sheets, see
yogi_Working With Source Data As A Formula Output In New versus Old Google Sheets - part 4
http://yogi--anand-consulting. blogspot.com/2014/01/ yogiworking-with-source-data- as-formula_3573.html
and
for use in Old Google Sheets, see
yogi_Working With Source Data As A Formula Output In New versus Old Google Sheets - part3
His solution has led me to another question: I would like to skip the Name data (Column E), so its not repetitive, in the following equation :
=ArrayFormula(query(filter(' Master Data'!B:I,('Master Data'!B:B>=G3)*('Master Data'!B:B<=G4)*('Master Data'!E:E= G2)+(row('Master Data'!B:I)=1)),"select "&join(",","Col"&FILTER(row(B: B),B:B="x"))))
=ArrayFormula(query(filter(' Master Data'!B:I,('Master Data'!B:B>=G3)*('Master Data'!B:B<=G4)*('Master Data'!E:E= G2)+(row('Master Data'!B:I)=1)),"select "&join(",","Col"&FILTER(row(B: B),B:B="x")), *"skip Col5"))
I have updated my sheet and expected results. Please take a peek!
----------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment