I guess you could do this without even using the pivot table using sumproduct
functions. This issue is probably better done in Microsoft Access though.

"William Horton" wrote:

I'm not sure if you can do this completely with a pivot table. However, if
you set your pivot table up so that Date is the page field, item and source
are the row fields, destination is the column field, and sum of Qty is the
data field then in another place in your worksheet you could make a table.
You would have to list all the items down a column (column A) and all the
bins across a row (row 40). Therefore, you would have to know all the
possible items and bins in advance. Then you could use GetPivotData
functions to get the answers you are looking for.

=IF(ISERROR(GETPIVOTDATA("Qty",$A$30,"Item",$A41," Source",B$40)),0,GETPIVOTDATA("Qty",$A$30,"Item",$ A41,"Source",B$40)*-1)+IF(ISERROR(GETPIVOTDATA("Qty",$A$30,"Item",$A41 ,"Destination",B$40)),0,GETPIVOTDATA("Qty",$A$30," Item",$A41,"Destination",B$40))

Where $A$30 is just identifying the pivot table, $A41 and B$40 identifies
the item and bin which you can copy to other cells. When I did this I
obtained the exact results you listed in your post.

If you filter the pivot table using dates the formulas update accordingly.
However, the data may not be what you expect when you filter the dates.
You'll have to check it out.

I couldn't find any way to acomplish this with just a pivot table with the
way that the data table is set up.

Hope this helps.

Bill Horton

"RD Wirr" wrote:

I have a large table of inventory transaction data that generally looks like
this
Data
Date Item Source Destination Qty
20-2-07 Item1 Bin1 Bin2 2
20-2-07 Item1 Bin1 Bin2 2
21-2-07 Item1 Bin2 Bin3 3
20-2-07 Item2 Bin1 Bin2 1
20-2-07 Item2 Bin1 Bin2 2
21-2-07 Item2 Bin2 Bin3 2

Each transaction includes the Items that are transferred from one Bin to
another and the Source and Destination Bins are named and the Qty of each
item transferred.
I subtract the Qty of items from the source Bin and Add the same items to
the Destination Bin in each transaction
I am trying to make a PivotTable that summarrizes these transactions and
calculates the net Qty of items in each Bin that would give these results
from the data above.
I also need to filter this data within Start and End dates.

PivotTable
Date
Sum of Qty Source
Item Bin1 Bin2 Bin3
Item1 -4 1 3
Item2 -3 1 2

Can anyone give me an idea how to approach this with the PivotTable
calculations?
Thanks in advance.
RDW