Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
THanks but not looking that. –  Gok Demir Feb 5 '10 at 15:56
    
Eh? (Do you mean "this won't work for me"? If so, why not?) –  Martha Feb 5 '10 at 16:22
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.