On Feb 14, 11:53 am, "Herbert Seidenberg"
wrote:
Try Pivot Table.
No complicated formulas required.
Assume table A look like this:

Project P/N Qty_Rqd
AGC D151 32
B298 28
A375 37
K566 18
J726 35
E332 36
TRA G297 16
B847 15
K566 34
B298 14

Table B:
P/N Qty_Inv
D151 149
B298 446
A375 112
K566 623
J726 249
E332 232
G297 323
B847 126

Data Pivot Table Multiple Consolidation Ranges
Define first Range as the last 2 columns of table A.
The second Range is table B.
The header of the first column of the two Ranges
should always be the same (i.e. P/N)
Include extra rows for future expansion.
Uncheck/Hide Grand Totals, Subtotals
The resulting PT might look like this:

Sum of Value Quantity
P/N Qty_Inv Qty_Rqd Qty_Hnd
A375 112 37 75
B298 446 42 404
B847 126 15 111
D151 149 32 117
E332 232 36 196
G297 323 16 307
J726 249 35 214
K566 623 52 571
(blank) 0

The Qty_Hnd is an added field.
Click on Qty_Rqd, and from the PT tool bar
Formulas Calculated Item
=Qty_Inv-Qty_Rqd
After you add more projects, refresh the PT.

thanks again...

having a bit of difficulty still....the pivot table works well, but
there are some parts that are not consolidating. for example the
pivot table will show

Sum of Value Quantity
P/N Qty_Inv Qty_Rqd Qty_Hnd
A375 112 75
B298 446 42 404