Hi Bill,
Thanks for the input. This works well. The GetPivotData seems to take a bit
of time (I have a lot of data) but it is still 10 times faster than all the
descrete Dsum functions I have been using. As you mentioned this could be
done completely with Sumproduct's but because of the complexity of the actual
data (12 bins, 1000's of items and 10's of 1000's of data records) Sumproduct
is too slow and I have been using Dsum which more easily allows multiple
conditions and runs faster. I agree it would be better done in Access. I am
trying to work out the basic functionality in Excel first (which I am a
little better with) and then after we've proved it out for some months, port
it over to Access.

I am wondering if you can tell me a way to add a calculated condition to the
GetPivotData formulas? I still need to filter the data by a range of dates.
In other words I need to enter a Start date and End Date in a pair of cells
and then limit the data shown to the data records between those two dates. I
tried putting the Date field on the page field but is really cumbersome
having to go into Field Setting and select all the dates to hide. Each record
in my data has a date/time stamp and there are many 1000's.

Thanks again,
RDW

"William Horton" wrote:

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