Wednesday, February 5, 2014

yogi_Working With Source Data As A Formula Output In New versus Old Google Sheets - part 5

                                         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. 

=query(query(index('Copy of Master Data'!B:AE),"select "&join(",","Col"&query(index(if(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/spreadsheets/d/1nx2suoNLcXmnX19ZE2mCTlsPXEdPJenYeXyZdF2zyXE/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: 

for use in New Google Sheets, see
yogi_Working With Source Data As A Formula Output In New  versus Old Google Sheets - part 4
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"))))
I have tried 
=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!
----------------------------------------------------------------------------------------------------------------------