Sunday, March 31, 2013

yogi_Pull Select Data In Multiple Sets From Another Sheet Based On A Specified Criterion



                                          Google Spreadsheet   Post  #1090
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 31, 2013
user Stephen Seattle1 :(http://productforums.google.com/forum/?zx=h0o3wpzxfaq#!category-topic/docs/spreadsheets/o-FoyenUc1Y)
IF formula extracting data from another sheet

I have multiple sheets within one spreadsheet and I am trying to use formulas to extract data from one sheet to another based on the date of a transaction.  I’m stumped in figuring out the formula needed.



My spreadsheet can be found at the following link:




On the “Press Orders” Sheet, I am looking to import data from the "Sales Data" sheet to populate the columns based on the date that I enter in cell A4. You will note that I already entered a formula in the other date cells A12, A20, A28, and A36. The rows under each date section should be populated based on a match of the same date located in the “Sales Data” sheet under columns K and M.  For example, for the date 3/2/2013, there should be 2 orders populating just two of the rows in the “Press Orders” sheet under the 3/2/2013 heading (Mila Adamova and Amanda Adams, since they have 3/2 press dates listed in column K in the “Sales Data” sheet).



 So, based on the dates listed in columns K and M in the “Sales Data” sheet, I am then trying to do the same import process for the other sections (dates) on the “Press Orders” sheet (there are 5 date sections starting on rows 4, 12, 20, 28, and 36).



For each "Press Orders" sheet column, I am trying to import the data as follows.  Note all this data is from the same row/transaction in the “Sales Data” sheet:

1. Name – imported from column A in the ‘Sales Data’ sheet

3. Inv # - imported from column C in the ‘Sales Data’ sheet

4. Cleanse Type - imported from column E in the ‘Sales Data’ sheet

5. Pack Details - imported from column F in the ‘Sales Data’ sheet

6. # of Bottles - imported from column H in the ‘Sales Data’ sheet

7. Current Order Comments - imported from column G in the ‘Sales Data’ sheet

8. Shipping Method - imported from column I in the ‘Sales Data’ sheet

9. Shipping Address - imported from column Y in the ‘Sales Data’ sheet

10. Phone - imported from column U in the ‘Sales Data’ sheet



Can someone point me in the right direction?  If this doesn’t make any sense, please let me know and I can try and explain it better.



Thank you!

Stephen
---
I tried the following formula and it didn't work:  =ArrayFormula(IFERROR(QUERY(IF({1,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, Col10, Col3, Col5, Col6, Col8, Col7, Col9,Col24, Col20 where Col8 > -1ʺ)))
--------------------------------------------------------------------------------------------------------------------------------------


following is a solution to the problem