Hi Herbert,

You have helped me in the past and I have been really impressed with your
solutions. In this case though, my actual data will cause problems with this
solution.

You are correct, on a smaller scale my data looks pretty much how you show
it but my actual data has close to a year of dates which will exceed the 256
column limit. Actually my dates are all date/time stamps that make is over
20,000 unique records. I have been playing with your idea of making a Gains
and losses PivotTables and then consolidating them but, I can't get over the
limitation of too many dates. In the end I still have to be able to filter
the resulting data to within a range of start and end dates. (not time stamps
but only whole days). Do you have an idea of another field I could use for
columns? I already tried swapping the date with the items but here again, I
have 1000's, too many.

I already have this functionality running with a sheet full of DSUM
operations (one row of Dsums for every item and one column of Dsums for every
Bin) but this is pretty cumbersome and really slow. The PivotTable seems to
be much faster and more easy to maintain than the Dsums. If you have another
idea, I am all eyes.

Thanks,
RDW

"Herbert Seidenberg" wrote:

Assume your data looks like this:
Date Item Source Destination Qty
20-2-07 Item1 Bin1 Bin2 16
20-2-07 Item1 Bin1 Bin2 21
21-2-07 Item1 Bin2 Bin3 10
21-2-07 Item2 Bin1 Bin2 28
20-2-07 Item2 Bin1 Bin2 17
21-2-07 Item2 Bin2 Bin1 26
21-2-07 Item1 Bin3 Bin1 22
20-2-07 Item2 Bin3 Bin2 30
20-2-07 Item1 Bin1 Bin3 23
20-2-07 Item2 Bin3 Bin1 25

Create 2 Pivot Tables, for Gain and Loss.
Gain might look like this:

Item (All)
Gain Date
Destination Source 20-2-07 21-2-07 Grand Total
Bin1 Bin1
Bin2 26 26
Bin3 25 22 47
Bin1 Total 25 48 73
Bin2 Bin1 82 82
Bin2
Bin3 30 30
Bin2 Total 112 112
Bin3 Bin1 23 23
Bin2 10 10
Bin3
Bin3 Total 23 10 33

Layout:
ROW = Destination, Source
COLUMN = Date
DATA = Sum of Qty (relabel Gain)
PAGE = Item
Options:
Grand Totals for Rows
Click on Destination/Source. For both Field Settings:
Subtotals: Automatic
Show items with no data

Create the Loss Pivot Table:
Same as Gain, but with
ROW = Source, Destination

Create this:
NetGain
Bin1 -32
Bin2 76
Bin3 -44

To create the first formula (-32), click on (Grand Total)
of (Bin1 Total) of Gain (73) and subtract the corresponding
cell at Loss (105).
You can select the items and dates of interest from
the drop down lists.