I have one sheet for products purchased and another one that sold. I want to make a pivot table from two sheet to get current inventory for each product. But options are only sum, average etc there is no subtraction. How could I do that? I am using Excel 2007. Thanks
Sounds like your problem is that the data is in two places. Possible solutions: a) Make 2 Pivot Tables for the two chunks of data. Use GetPivotData formulas to extract the values for the matching items (eg by product code) and subtract them.
b) Carry some of the data across from one sheet to the other using eg VLOOKUP to fetch the starting inventory figure across to your sales table, but only for the first occurence of a product being sold. Include this extra column in your sales pivot and add a calculated field to subtract them.
I prefer (a) but you would have to do some prep to create a list of unique product codes (paste sales + inventory together and remove duplicates then sort) so you can point the GPD functions at those cells.
The simplest would be to store the number sold as negative numbers.
If they're currently all positive numbers, you can easily change them to negative by typing -1 in an empty cell, copying it, and doing a Paste Special - Multiply on the number sold column.