Tuesday, October 23, 2012

Google Spreadsheet Formula for calculation from cells between different sheets



Question:


( by _min_  )


Dear all,

I'm new on Docs/spreadsheets, so be patient.

We have an on-line spreadsheet for sales of several products (well described with their Code Article, Description, Quantity) and duplicated in two identical sheets, let say one for Adam and the latter for Eve.
Each guy can fill its sheet according with its own sales, so that Article 1002 can be 10 for Adam and 0 for Eve,  Article 2932 5 for Adam and 7 for Eve, and so on.
Once a month the boss want to have the report of sold articles AS SUM, does not matter who sold what.
And he wants to get a report CONTAINING ONLY sold products, regardless to the other thousands unsold.

Very simple, in principle.
In practice I do not understand how to do that.

Any idea?




Solution:

Have a look at the following the screenshot of the Sheet "Adam":






Have a look at the following the screenshot of the Sheet "Eve":



Now go to "Tools" menu >> "Script Gallery" >> search for "VMerge" and install it. You have to authorize the script for the first time.

Now in your sheet "Master", put the following formula in cell "A1":
=query(VMerge(Adam!A2:C;Eve!A2:C);"select Col1,sum(Col3) where Col3 >= 0 group by Col1 label Col1 'Code Article' , sum(Col3) 'Quantity' ")


Have a look at the following the screenshot of the Sheet "Master" having your desired results:





I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 


Thanks,
Kishan,

No comments:

Post a Comment