Complicated Excel Formula Needed

Question

I am putting together an inventory and production spreadsheet.  

On Sheet1 I have all my raw materiel lot numbers and initial quantity, and on Sheet2 I have my production which includes the lot used and quantity.  

I need to get Sheet1 to display how much of a lot is left after I enter it into Sheet2.

(A) <br><br><br>Raw Material Lot Number	(B)<br><br><br>Initial Raw Material Quantity	(C)<br><br><br>Raw Material Remaining	(D)<br><br><br>(Production) Lot Number	(E)<br><br><br>Raw Material Used
1111	12	4	1111	2
2222	14	2	2222	4
3333	16	16	1111	6
4444	18	18	2222	8

What Formula Do i put in column (C) to get it to display correctly.  

Is this even possible? 

Thank you!

Answer 1

Hi FlourMiller

Assuming Sheet2 looks like the picture below

Then in Sheet1 type this formula in column C

=B2-SUMIFS(Sheet2!$B:$B,Sheet2!$A:$A,Sheet1!A2)     and copy down

as shown in the picture below

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 2

Hi FlourMiller

I hope you are ok, sound and healthy

It has been almost a week since you contact us looking for help

We have responded to your question, but unfortunately, we have no reply from you.

Did you find solution to your problem?

Please, leave a reply if you need more help.

Regards

Jeovany

Answer 3

Assign one lot number to each column.  Use a single sheet.  First line "purchase lot number"; second line "initial quantity"; third line "quantity remaining".   Lines 4 and beyond will be for material used each withdrawal.  Line 3 is line 2 minus sum(line 4:line (pick a number).

Answer 4

Hi Flour,

Have you tried Jeovany's advice to check the result?

Please leave a reply if you need more help.

Best Regards

Higgins