Formula for auto adjusting inventory vs parts used/returned on two separate sheets

[JC2684]

HI there pretty new to this.

I'm pretty sure I'm either supposed to use 'sumif' or 'if' and 'and' but am at a loss.

I have two separate sheets in the same excel file and i want the 'inventory' sheet to be automatically adjusted by the 'Parts taken or returned' sheet. I want this to be done by 'part number' in the 'Parts Taken or Returned' sheet. So after entering the 'part number' it auto searches the inventory sheet for the corresponding 'part number' then once a number is put into the the 'quantity taken' cell or 'quantity returned' cell the cell in the 'inventory' sheet substracts that number from the 'quantity' or adds the returned part/s back to the 'quantity' cell respectively to the 'inventory' 'quantity' cell.

I also wrote some comments on the attached excel sheet which might be easier to follow

Thanks for your help!

[nick.williams]

You can't use a formula to update the inventory unless you store somewhere else what it was before. e.g. have a column for inventory at start of month, you could reference this and then use the parts taken and returned in the month to give inventory at the end of the month.

If you want it to update without storing, then you will need to use VBA, if that's an option for you.

[JC2684]

i think i understand. Would you be able to look at the newly attached file and see if I'm on the right track? If need be I'll look into VBA, I'm pretty sure i know someone who might be able to help they're really busy.

[JohnTopley]

Before you go too far: there are numerous templates (and videos!) available on the Internet which address the problem of inventory management.

[nick.williams]

...but yes, you are on the right lines if that's what you want to do. e.g.

=SUMIFS('Parts taken or returned'!$C$2:$C$4,'Parts taken or returned'!$A$2:$A$4,$A2,'Parts taken or returned'!$B$2:$B$4,$B2)