Monday, June 17, 2013

yogi_Pull Data From Another Spreadsheet -- Select Columns Meeting Specified Criteria

                                          Google Spreadsheet   Post  #1246
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 17, 2013
user Atio (http://productforums.google.com/forum/?zx=qbs7kko366y6#!category-topic/docs/spreadsheets/YosZMa7O9Fw)
String for multiple filters on spreadsheet?

Hello,

I am trying to build a "report" sheet, which I could embed into a Google Site.

I have a spreadsheet which contains 18 columns of data (A - R).  It's a sales/fulfillment sheet, showing things like Product ID, Sales amount, Sales Rep Name, Process Date, and Ship Date (and quite a bit more.)

(to hopefully make things clear, let's call the full 18-column sheet "Source"    and the sheet I'm trying to generate  "Report"):

Here's what I'm trying to do:

First, on the "Report" sheet, I am calling only certain columns from the "Source"....using this string:    =FILTER( Source!A:R ; {1,1,1,0,1,1,1,1,0,0,1,0,1,1,0,0,0,0} )

This part works OK.

I also want to be able to see a filtered view of the "Report" sheet, filtering TWICE, using values from two columns.  From the 'raw' version of the "Report" sheet, I would like to first filter by "SalesRepName" (Column F on "Source" and Column E on "Report) and then I would like to filter that list further, showing only BLANK entries on "DateShipped" (Column N on "Source" and Column J on "Report").

So far, I have been able to generate this "filtered" view, by applying the two filters directly to the columns on the "Report" sheet.
*******This is where I need the most help:
The biggest thing I need, is for this filtered sheet to get updated when I add values to the "Source" sheet. When I manually apply the filters to Column E and Column J on "Report" sheet, anything I add to the "Source" sheet will not show up (even if it meets the criteria) UNLESS I remove the filters from the columns, then re-apply them.

I want to be able to embed this filtered sheet onto a google site for my sales reps to see, so I need it to update itself......any help would be very much appreciated!

---
Hi Yogi, thanks for the assistance.

Here is a sample of the "Source" spreadsheet:
https://docs.google.com/spreadsheet/ccc?key=0AqLcMq-8O40zdEdsRkwwX2dVTXJmZ3M0WTJhNENwaHc&usp=sharing

And here is what I need:

In a separate spreadsheet (not another sheet in the same document) I would like to import the following columns from the "Source" sheet:  A, B, C, E, F, G, H, K, M, N    The "Import" sheet link is below, I am calling it "Destination"  (<--- this is also the mockup of what I would like the results to be.  It's manually built just to demonstrate what I'm looking for)

https://docs.google.com/spreadsheet/ccc?key=0AqLcMq-8O40zdGR0UEItNkJvSWN4QjNRQk1ET2lRNmc&usp=sharing

I also am looking to have this import be filtered:  I would like the "Destination" sheet to show ONLY rows for a certain value for Sales Rep (Column F in the "Source" sheet)....AND only those rows which are also blank for the "Shipped" field (Column N in the "Source" sheet)  The sample "Destination" sheet (above) is an example, showing only the entries for "Sales Rep 1" which are also blank in the "Shipped" column.

The goal I am trying to achieve:  Someone enters a sale/order information into the "Source" sheet, and each of the individual sales reps will be able to use their "Destination" sheet to see ONLY their orders which have not yet been shipped.  For this to work, I need the "Destination" sheet to update automatically when there is a change made to the "Source" sheet which matches the filter criteria.  (My eventual goal is to create a separate "Destination" sheet for each of the sales reps, so that I can embed them on individual pages of a Google Site.)

-------------------------------------------------------------------------------------------------------------------------