Inventory management how to deduct stock from sheet 1 and amend stock value on sheet two

[daire]

Hi experts,
I am really struggling with a formula for the following situation.
I am trying to create a Job report on sheet 1 called "service order" On this service order i have created a table with the following information,
Column 1 is (drop down)part type,column 2 is dependent on part type(column 1) and is part description again drop down, column 3 is part number drop down dependent on previous selection. Column 4 is unit type and auto fills on column 1 selection. Now Column 5 is the quantity used.
So on sheet 2 i have called "stock list" which has the stock amount for each item.
What i want to do is if i use a part on sheet 1 (service order) i want to deduct the amount used for that part on sheet two.
My difficulty is that the items on sheet 1 are dynamic with the drop down boxes.
I am fairly new at excel so any help would be great.tks

 

[daire]

Sheet 1 (Service order)
A B C D E
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 667"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: left"]Part Type[/TD]
[TD="class: xl65, width: 207, align: left"]Item Description[/TD]
[TD="class: xl65, width: 98, align: left"]Part NO[/TD]
[TD="class: xl65, width: 116, align: left"]Units[/TD]
[TD="class: xl65, width: 81, align: left"]Qty Used[/TD]
[TD="class: xl65, width: 54, align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 613"]
<tbody>[TR]
[TD="width: 111, align: left"]Electrical[/TD]
[TD="width: 207, align: left"]DC Fan Motor 70W KFD-325-70-8C[/TD]
[TD="class: xl65, width: 98"]5017652[/TD]
[TD="class: xl65, width: 116"]Ea[/TD]
[TD="width: 81, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 613"]
<tbody>[TR]
[TD="width: 111, align: left"]Pipework[/TD]
[TD="width: 207, align: left"]5/8" White Shield[/TD]
[TD="class: xl65, width: 98"]5/8WS[/TD]
[TD="class: xl65, width: 116"]Metres[/TD]
[TD="width: 81, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (stock)
A B C D E F G H
[TABLE="width: 1097"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Item Description[/TD]
[TD="align: left"]Part Number[/TD]
[TD="align: left"]Unit[/TD]
[TD="align: left"]Reorder point[/TD]
[TD="align: left"]Reorder amount[/TD]
[TD="align: left"]Qty Used[/TD]
[TD="align: left"]Stock In[/TD]
[TD="align: left"]Current stock[/TD]
[/TR]
[TR]
[TD="align: left"]Column1[/TD]
[TD]Column2[/TD]
[TD="align: left"]Column3[/TD]
[TD="align: left"]Column4[/TD]
[TD="align: left"]Column5[/TD]
[TD="align: left"]Column6[/TD]
[TD="align: left"]Column7[/TD]
[TD="align: left"]Column8[/TD]
[/TR]
[TR]
[TD="align: left"]PCB ECO8102[/TD]
[TD]5001771[/TD]
[TD="align: left"]ea[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]Flow Switch GBS[/TD]
[TD]5011056[/TD]
[TD="align: left"]ea[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]Flow Sensor Sika VVX20[/TD]
[TD]5015066[/TD]
[TD="align: left"]ea[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]3 way switch valve[/TD]
[TD]5017354[/TD]
[TD="align: left"]ea[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]DC Fan Motor 70W KFD-325-70-8C[/TD]
[TD]5017652[/TD]
[TD="align: left"]ea[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[daire]

Hi all,
I will try to rephrase what i am trying to do. I have two sheets. Sheet1 = service order, sheet 2 = stock list.
If Sheet1(service order) Cell 1A = any cell in Sheet2 (stock list) Column A, then deduct entered value in cell E1 (sheet1) from value in column G(sheet2) for that part and enter value into current stock (sheet 2)Column H for that selected part.
So recap cell 1A on sheet 1 is "part description" this is selected from a drop down if that matches a cell in Column A (also part description list) on sheet 2 then the value (amount used) entered for that item in cell E1 sheet1 is deducted from the issued amount in Column G (sheet 2) and the value is updated for that item in the corresponding cell in H.

Formula is probably something like this, IF 1A(sheet1)=ColumnA(sheet2) then ColumnG (sheet2)- E1(sheet1)= value column H sheet2

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...eet-1-and-amend-stock-value-on-sheet-two.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[daire]

Cross posted https://www.excelforum.com/excel-fo...eet-1-and-amend-stock-value-on-sheet-two.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

My apologizes i have received a similar message on the other forum. I am only new and should have read the rules. I have not posted this question on any other forums. I don't mean to waste anybodies time. This was a genuine mistake.