1

I am trying to set up an inventory spreadsheet using 3 worksheets. the first one has the items in inventory and their counts. the second sheet consists of the number of items used at an event which need to be subtracted from the first page of the inventory. the third sheet is tracking items returned or purchased which need to be added to the first page of the inventory. I

I have been able to create a formula for one update to the item but can not figure out how to make the formula work for subsequent transactions.

iris57
  • 11

1 Answers1

0

I am thinking that this is a rental business. Here is the way I envision the worksheets:

Sheet1 enter image description here

Sheet2 enter image description here

Sheet3 enter image description here

If I am correct, the formula to update the inventory for sheet1!C2 is

  • =SUMIFS(Sheet3!$C$2:$C$14,Sheet3!$B$2:$B$14,Sheet1!B2)+SUMIFS(Sheet3!$D$2:$D$14,Sheet3!$B$2:$B$14,Sheet1!B2)-SUMIFS(Sheet2!$C$2:$C$14,Sheet2!$B$2:$B$14,Sheet1!B2)

    which can be copied down to populate Sheet1!C3.

Community
  • 1
Clif
  • 596