Wednesday, October 31, 2012

yogi_Pull Data From Sales And Customer Sheets By Dates In User's Report Format


                                           Google Spreadsheet   Post  #841
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 31, 2012
user Stephen Seattle said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/DnnLepXiVA8)
Multiple sheets and formulas
I just mess up my spreadsheet and can't figure out how to fix it.  And since I'm new to Google Docs, I attempted to fix the problem but failed.  I'm stuck and would greatly appreciate any expert out there who could help me resolve my problem.

The premise: I have a spreadsheet with multiple sheets.  The link to my spreadsheet is below.  In my sheet called 'Press Orders', I am importing data from 2 other sheets ('Sales Data' and 'Customer Database') based on a date that I manually enter in cell A4 on the 'Press Orders' sheet.  The formula I have in A5 (then copied to cells A18, A31, A44, A57), is working perfectly for importing data into columns A, B, F, and G.

But in columns C, D and E, the data isn't importing correctly.  I need the 'Sales Data' columns H and J to be imported into 'Press Orders' columns D and E.  Then for column C, the formula is based on the matching date from A4 (as well as A18, A31, A44, A57) on the 'Press Orders' sheet to the date columns K and M of the 'Sales Data' sheet, I want the # of bottles listed in 'Sales Data' columns L and N to import to column C on the 'Press Orders' sheet for any matching dates.  I need the formula in cell A5 to be corrected to accomplished this.  (I can then manually copy it to the A18, A31, A44, and A57.)

The formula I'm currently using is:
 =ArrayFormula(IFERROR(QUERY(IF({1,1,1,1,1,1,1,0,1};'Sales Data'!A3:J;IF(INT('Sales Data'!K3:K)=A4;'Sales DATA'!L3:L;IF(INT('Sales Data'!M3:M)=A4;'Sales DATA'!N3:N;-1)));"select Col1, Col6, Col9, Col8, Col10 where Col8 > -1")))


Any help with this would be awesome!  Please do not hesitate to ask me any questions.

Thank you!

Stephen
----------------------------------------------------------------------------------------------------
following is a solution to the problem
Posted by at
Labels: , , ,

3 comments:

  1. StephenSeattleOctober 31, 2012 at 7:57 PM

    Hi Yogi,

    Thank you for your formula alternative. I posted your formula:

    =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))))

    I only get error messages and nothing at all populate the fields. Can you kindly recheck your formula?

    Thanks,
    Stephen

    ReplyDelete
  2. Yogi AnandOctober 31, 2012 at 8:13 PM

    Hi Stephen:

    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.

    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.

    Make sure you are copying my formula correctly and applying it correctly in cell A5 of sheet Press Orders.

    Here is the formula (copied from my formula sheet):

    =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))))

    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.

    Cheers!
    Yogi

    ReplyDelete
  3. StephenSeattleOctober 31, 2012 at 8:59 PM

    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"?

    Cheers,
    Stephen

    ReplyDelete

Subscribe to: Post Comments (Atom)