tag:blogger.com,1999:blog-8807805591547673516.post7402020278581520558..comments2024-02-28T08:14:50.756-08:00Comments on Cloud Computing -- Google Docs Way: yogi_Pull Data From Sales And Customer Sheets By Dates In User's Report FormatYogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-8807805591547673516.post-61609875899804450252012-10-31T20:59:59.965-07:002012-10-31T20:59:59.965-07:00Thanks, Yogi. After copying your formula it worke...Thanks, Yogi. After copying your formula it worked. I must have had a typo in it when I tried before. However, your formula is looking for a matching date in 'Sales Data' column B, and importing the # of bottles from column I. What I need the formula to do is look for a matching date in both columns K and M, then import the bottles quantity listed in columns L and N. Is that an "easy fix"?<br /><br />Cheers,<br />StephenStephenSeattlehttps://www.blogger.com/profile/12127522085977419573noreply@blogger.comtag:blogger.com,1999:blog-8807805591547673516.post-28011871552436795942012-10-31T20:13:20.468-07:002012-10-31T20:13:20.468-07:00Hi Stephen:
I don't know how you produced my ...Hi Stephen:<br /><br />I don't know how you produced my formula in your preceding comment -- please copy the formula from my formula sheet and paste it into cell A5 of your sheet Press Orders.<br /><br />My formula works fine ... as you can see the results in my blog post. There are a lot of discrepancies in your data ... so you may not get all the results correct but using my formula you should get some correct results.<br /><br />Make sure you are copying my formula correctly and applying it correctly in cell A5 of sheet Press Orders.<br /><br />Here is the formula (copied from my formula sheet):<br /><br />=ArrayFormula(if({1,2,3,4,5,0,0},query('Sales Data'!A$5:J,"select A,F,I,H,J where todate(B)=date '"&text(A4,"yyyy-mm-dd")&"' "),if({0,0,0,0,0,1,0},vlookup(filter('Sales Data'!A$5:A,int('Sales Data'!B$5:B)=A4),'Customer Database'!C:M,{2}*row(indirect("A$1:A"&counta(filter('Sales Data'!A$5:A,int('Sales Data'!B$5:B)=A4))))^0,0),vlookup(filter('Sales Data'!A$5:A,int('Sales Data'!B$5:B)=A4),'Customer Database'!C:M,{11}*row(indirect("A$1:A"&counta(filter('Sales Data'!A$5:A,int('Sales Data'!B$5:B)=A4))))^0,0))))<br /><br />If you can not get it to work, you need more help on your project than answer to a specific technical question ... if so contact me via my vCita contact and then let us take it from there.<br /><br />Cheers!<br />YogiYogi Anandhttps://www.blogger.com/profile/10862524957172908621noreply@blogger.comtag:blogger.com,1999:blog-8807805591547673516.post-69661159721764376182012-10-31T19:57:56.473-07:002012-10-31T19:57:56.473-07:00Hi Yogi,
Thank you for your formula alternative. ...Hi Yogi,<br /><br />Thank you for your formula alternative. I posted your formula:<br /><br />=ArrayFormula(if({1,2,3,4,5,0,0},query('Sales Data'!A$5:J,"select A,F,I,H,J where todate(B)=date '"&text(A4,"yyyy-mm-dd")&'" "),if({0,0,0,0,0,1,0},vlookup(filter('Sales Data'!A$5:A,int('Sales Data'!B$5:B)=A4),'Customer Database'!C:M,{2}*row(indirect("A$1:A"&counta(filter('Sales Data'!A$5:A,int('Sales Data'!B$5:B)=A4))))^0,0),vlookup(filter('Sales Data'!A$5:A,int('Sales Data'!B$5:B)=A4),'Customer Database'!C:M,{11}*row(indirect("A$1:A"&counta(filter('Sales Data'!A$5:A,int('Sales Data'!B$5:B)=A4))))^0,0))))<br /><br />I only get error messages and nothing at all populate the fields. Can you kindly recheck your formula?<br /><br />Thanks,<br />StephenStephenSeattlehttps://www.blogger.com/profile/12127522085977419573noreply@blogger.com