Copy and past from different sheets based on different criteria then sum certain columns

I'm having a difficult time understanding the description in your file, but based on your expected results, I put the following formula in D2, then filled down:

=IF(IFERROR(VLOOKUP(A2,Sheet3!$A$2:$D$25,4,FALSE)-SUMIF($A$1:$A1,$A2,$D$1:$D1),0)>0,MIN(C2,VLOOKUP(A2,Sheet3!$A$2:$D$25,4,FALSE)-SUMIF($A$1:$A1,$A2,$D$1:$D1)),"")

And in E2 and down:

=IF(D2="",C2,C2-D2)

Take a look at the attachment to see if I'm on the right track. If I'm not, please try to provide a clear explanation of what is missing and a few more expected results.

I believe the formulas I provided in post #2 provide the results you're looking for. I've applied them to the attachment in post #3 and they seem to fit what you're describing. Can you please look at the attachment and explain exactly what it is doing incorrectly?

If I'm understanding correctly, then select A2:E23, select conditional formatting --> new rule --> use a formula, then use the following formula:

=VLOOKUP($A2,Sheet3!$A$2:$D$7,4,FALSE)>SUMIF($A$1:$A1,$A2,$D$1:$D1)+SUMIF($A2:$A$23,$A2,$C2:$C$23)

I'm not sure whether you just want to highlight just the zero-quantity rows that qualify or all rows with the item# that qualifies. The formula above will do the latter. Hopefully that's what you're looking for? Experiment with the attachment to see if it works as desired:


EDIT: You can delete column F in the attachment, it's just a test column indicating which rows (those marked TRUE) will trigger the formatting.

Excellent, glad to help! Good luck!