I have got another problem that i am hoping you could help out. I hope
i can phrase it as detail as possible. I have a master worksheet that
hold the list of inventory(bill of material) and the corresponding
quantity that i have on hand like say we name it (MasterInventory). The

value in the MasterInventory is dynamic, quantity will be deducted went

a certain component is used in the production of a product and will
increase when supply come in. The data of the supply come in the form
of another excel worksheet. It is broken down into dates that they will

be deliver. Example is as follow:


MasterInventory (Before)
model part number/description Quantity 12/1 13/1 15/1
LP120 12-1234-12 12 12 12 12
LP120 13-1234-14 05 05 05 05
M1 15-1234-12 10 10 10 10


009C 14-1234-15 01 01 01 01


SupplyData
Mon Tue Wed Thu
part number/description 12/1 13/1 15/1 17/1
12-1234-12 02 02 03 04
13-1234-14 01 00 03 01
14-1234-15 00 01 03 00


What i am trying to do is something like a postman. Sorry if i use
inappropriate terms. The SupplyData are like the letters he has to
deliver and the MasterInventory is the letter box with different pigion

hole that he can slot the letter accordingly. Meaning the quantity in
the MasterInventory will find matching part number from the SupplyData
and add up its current quantity(MasterInventory) with the new quantity
that is due to deliver(SupplyData) according to the date.


MasterInventory (After)
model part number/description Quantity 12/1 13/1 15/1
LP120 12-1234-12 12 14 16 19
LP120 13-1234-14 05 06 06 09
M1 15-1234-12 10 10 10 10


009C 14-1234-15 01 01 02 02


The reason that i am trying to do this to relief the user from data
entry as this will help to reduce human error. Thanks if you could
help.

Perhaps one way which might work ..

A sample construct is available at:
http://cjoint.com/?cboUVLKZvi
Updating inventory quantity_kuansheng_wks.xls

In sheet: MasterInventory,

Put in D2 (normal ENTER):
=IF(OR($C2="",$B2="",D$1=""),"",SUM($C2,IF(OR(ISNA (MATCH(D$1,SupplyData!$2:$
2,0)),ISNA(MATCH($B2,SupplyData!$A:$A,0))),0,INDEX (OFFSET(SupplyData!$A:$A,,
MATCH(D$1,SupplyData!$2:$2,0)-1),MATCH($B2,SupplyData!$A:$A,0)))))

Copy D2 across and fill down to populate

(Quantity figs in col C are assumed manually maintained)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kuansheng" wrote in message
oups.com...
I have got another problem that i am hoping you could help out. I hope
i can phrase it as detail as possible. I have a master worksheet that
hold the list of inventory(bill of material) and the corresponding
quantity that i have on hand like say we name it (MasterInventory). The

value in the MasterInventory is dynamic, quantity will be deducted went

a certain component is used in the production of a product and will
increase when supply come in. The data of the supply come in the form
of another excel worksheet. It is broken down into dates that they will

be deliver. Example is as follow:


MasterInventory (Before)
model part number/description Quantity 12/1 13/1 15/1
LP120 12-1234-12 12 12 12 12
LP120 13-1234-14 05 05 05 05
M1 15-1234-12 10 10 10 10


009C 14-1234-15 01 01 01 01


SupplyData
Mon Tue Wed Thu
part number/description 12/1 13/1 15/1 17/1
12-1234-12 02 02 03 04
13-1234-14 01 00 03 01
14-1234-15 00 01 03 00


What i am trying to do is something like a postman. Sorry if i use
inappropriate terms. The SupplyData are like the letters he has to
deliver and the MasterInventory is the letter box with different pigion

hole that he can slot the letter accordingly. Meaning the quantity in
the MasterInventory will find matching part number from the SupplyData
and add up its current quantity(MasterInventory) with the new quantity
that is due to deliver(SupplyData) according to the date.


MasterInventory (After)
model part number/description Quantity 12/1 13/1 15/1
LP120 12-1234-12 12 14 16 19
LP120 13-1234-14 05 06 06 09
M1 15-1234-12 10 10 10 10


009C 14-1234-15 01 01 02 02


The reason that i am trying to do this to relief the user from data
entry as this will help to reduce human error. Thanks if you could
help.

It works perfectly. How can i thanks you.

Glad it worked for you !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kuansheng" wrote in message
oups.com...
It works perfectly. How can i thanks you.