Wednesday, November 28, 2012

yogi_Extract Sales Records In Sheet 'Each1' From Sheet 'Each' by Specific Dates But Exclude Canceled Sales Logged In Sheet 'CXL'


                                          Google Spreadsheet   Post  #898

              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 28, 2012
user Chris Sansone said:(https://productforums.google.com/forum/#!mydiscussions/docs/bpQ-ZJeSAio)
How to exclude rows against an array? 
I'm working on a bunch of sheets that manipulate info from a master sheet with a bunch of information about orders.

It starts out by using QUERY statements to pull the master sheet apart into different date ranges, which then get picked apart by more QUERY statements to more sheets for analysis.

What I want to do, is have a list of cancelled order numbers in its own sheet and exclude rows with that order number in some of the queries.

What's the syntax for this?
---
Here's the link to the sheet:

Actually that's a small bit of sanitized sample data.

So info about each order is entered into the first sheet, named "each"

the sheet "dates" has periods of time, which are pulled into QUERY statements in cell A1 on the numbered sheets:

"each1" "each2" "each3" "each4" respectively.

Other stuff happens from there, I left a few sheets but they aren't important. What I'd like to happen is for the four numbered "each" sheets to pull rows from the main "each" sheet within the date frame (works already) but also where the order# in column B does not match any order number in column A of the sheet named "cxl"

"cxl sheet would be a maintained list of orders that were cancelled for some reason, and I don't want them included in any further calculations in this part of the operation.
----------------------------------------------------------------------------------------
following is a solution to the problem