software

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 10009C 14-1234-15 01 01 01 01SupplyData
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 00What 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 10009C 14-1234-15 01 01 02 02The 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:
cjoint.com/?cboUVLKZvi
Updating inventory quantity_kuansheng_wks.xls

In sheet: MasterInventory,

Put in D2 (normal ENTER):
=IF(OR($C2=quot;quot;,$B2=quot;quot;,D$1=quot;quot;),quot;quot;,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
savefile.com/projects/236895
xdemechanik
---
quot;kuanshengquot; gt; wrote in message oups.com...
gt; I have got another problem that i am hoping you could help out. I hope
gt; i can phrase it as detail as possible. I have a master worksheet that
gt; hold the list of inventory(bill of material) and the corresponding
gt; quantity that i have on hand like say we name it (MasterInventory). The
gt;
gt; value in the MasterInventory is dynamic, quantity will be deducted went
gt;
gt; a certain component is used in the production of a product and will
gt; increase when supply come in. The data of the supply come in the form
gt; of another excel worksheet. It is broken down into dates that they will
gt;
gt; be deliver. Example is as follow:
gt;
gt;
gt; MasterInventory (Before)
gt; model part number/description Quantity 12/1 13/1 15/1
gt; LP120 12-1234-12 12 12 12 12
gt; LP120 13-1234-14 05 05 05 05
gt; M1 15-1234-12 10 10 10 10
gt;
gt;
gt; 009C 14-1234-15 01 01 01 01
gt;
gt;
gt; SupplyData
gt; Mon Tue Wed Thu
gt; part number/description 12/1 13/1 15/1 17/1
gt; 12-1234-12 02 02 03 04
gt; 13-1234-14 01 00 03 01
gt; 14-1234-15 00 01 03 00
gt;
gt;
gt; What i am trying to do is something like a postman. Sorry if i use
gt; inappropriate terms. The SupplyData are like the letters he has to
gt; deliver and the MasterInventory is the letter box with different pigion
gt;
gt; hole that he can slot the letter accordingly. Meaning the quantity in
gt; the MasterInventory will find matching part number from the SupplyData
gt; and add up its current quantity(MasterInventory) with the new quantity
gt; that is due to deliver(SupplyData) according to the date.
gt;
gt;
gt; MasterInventory (After)
gt; model part number/description Quantity 12/1 13/1 15/1
gt; LP120 12-1234-12 12 14 16 19
gt; LP120 13-1234-14 05 06 06 09
gt; M1 15-1234-12 10 10 10 10
gt;
gt;
gt; 009C 14-1234-15 01 01 02 02
gt;
gt;
gt; The reason that i am trying to do this to relief the user from data
gt; entry as this will help to reduce human error. Thanks if you could
gt; help.
gt;
It works perfectly. How can i thanks you.Glad it worked for you !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;kuanshengquot; gt; wrote in message oups.com...
gt; It works perfectly. How can i thanks you.
gt;