I have a workbook with 3 worksheets, 1 main (total inventory), 2 sub
worksheets.
One of the sub worksheets tracks part #, store # and qty out, the other one
tracks part #, qty out, store # and tech id. I want to be able to insert
into the sub sheets and the main sheet have the items deducted.
Each day I physically enter new entries into the sub sheets, then re-enter
into the main sheet so the main sheet will show a true inventory.
Is there a way around all the duplicate entries?

Carol,
I'm assuming that one worksheet is for Goods In, and the other is for Goods
Out.

Goods In Worksheet
Date Store Part# Qty In
01/03/2010 A 1 20
01/03/2010 B 1 10
01/03/2010 A 2 30
01/03/2010 A 2 0

Parts total at stock take.

Goods Out worksheet
Date Part# Qty Out Store Tech ID
02/03/2010 1 5 A Harry
02/03/2010 1 20 B Fred


Main Worksheet
Part# Stock
1 5
2 30
3

Stock calculated with the following formula in B2 and copied down.

=SUMPRODUCT(--(GoodsIn!C:C=Main!A2),(GoodsIn!D:D))-SUMPRODUCT(--(GoodsOut!B:B=Main!A2),(GoodsOut!C:C))

Main is the sheet1 name.

HTH
Peter


"Carol" wrote:

I have a workbook with 3 worksheets, 1 main (total inventory), 2 sub
worksheets.
One of the sub worksheets tracks part #, store # and qty out, the other one
tracks part #, qty out, store # and tech id. I want to be able to insert
into the sub sheets and the main sheet have the items deducted.
Each day I physically enter new entries into the sub sheets, then re-enter
into the main sheet so the main sheet will show a true inventory.
Is there a way around all the duplicate entries?

Bill,
Thank you for your input, here is a little more detail on what I am looking
for.
Date Part # QTY out Store # Tech ID
42710 8534-0081 2 13084 4
42810 375R40 4 12710 2
42810 C3400P 1 10881 1
42810 C5308FRY 1 10881 1
42610 P90018 2 3242 6
The above is a portion of the TECH worksheet

40710 10261-07 6 10714
40510 10461-13 6 4885
40510 7016 12 4885
40510 X44127 1 11000
This is a portion of the STORE worksheet

The third worksheet in this book is the WAREHOUSE that has everything
combined and the main totals, thousands of different items.
The headings for the main worksheet are as follows:

Alternate Part Number Part Number Description Begin Inv Qty in Qty
out On hand

I need a way to have the 2 sub sheets update in the main sheet. I do not
know if I can make excel look for the correct column and add to the "OUT" on
the correct item all at the same time.

The subsheets have entries added on a daily basis. The are added by me
inserting blank rows at the top of the page and typing the info you see.

"Billy Liddel" wrote:

Carol,
I'm assuming that one worksheet is for Goods In, and the other is for Goods
Out.

Goods In Worksheet
Date Store Part# Qty In
01/03/2010 A 1 20
01/03/2010 B 1 10
01/03/2010 A 2 30
01/03/2010 A 2 0

Parts total at stock take.

Goods Out worksheet
Date Part# Qty Out Store Tech ID
02/03/2010 1 5 A Harry
02/03/2010 1 20 B Fred


Main Worksheet
Part# Stock
1 5
2 30
3

Stock calculated with the following formula in B2 and copied down.

=SUMPRODUCT(--(GoodsIn!C:C=Main!A2),(GoodsIn!D:D))-SUMPRODUCT(--(GoodsOut!B:B=Main!A2),(GoodsOut!C:C))

Main is the sheet1 name.

HTH
Peter


"Carol" wrote:

I have a workbook with 3 worksheets, 1 main (total inventory), 2 sub
worksheets.
One of the sub worksheets tracks part #, store # and qty out, the other one
tracks part #, qty out, store # and tech id. I want to be able to insert
into the sub sheets and the main sheet have the items deducted.
Each day I physically enter new entries into the sub sheets, then re-enter
into the main sheet so the main sheet will show a true inventory.
Is there a way around all the duplicate entries?