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