yogi_Perform Computations Based On Multiple Criteria From Data In A Number Of Sheets In The Spreadsheet

                                           Google Spreadsheet   Post  #837
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 29, 2012
user beanies said: (http://productforums.google.com/forum/?zx=g9a265uola4#!category-topic/docs/spreadsheets/mNLepVOURJI)
Google spreadsheet help needed. How to search multiple sheet and sum the result based on the critieria

The data is at here

https://docs.google.com/spreadsheet/ccc?key=0AhuKBdnnL_ggdGdFLVZXYVBFNzE4eTQxZWdKdUNYOXc#gid=0

It is a sample of what the data look like. say like I have three person here( the actual data may have more than 3 ppl), and each one has their own sheet to record their own inventory. Each day, if person i bought sth with unit x, he will record it in his own sheet( in the light pink back ground) in the form as" name      units     did he eat it or not" 

For example ,the first record of AAA tells us AAA bought 3 apples and he eat them all.

As he may buy the same thing in different day. Then I use UNIQUE function to find unique items in COL A6, then use FILTER function to find the corressponding total units

My problem is:

1.

I searched online and find how to show up unique col in "inventory"sheet" among different sheet( From AAA, BBB, and CCC)

but I don't know how to use  filter function to search all over the spreadsheet . Can someone help me with this?

2, In the inventory sheet. How can I make the col B-D updated automatically when col A updated a new cell?

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

following is a solution to the proble