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
  • If the answer that you’ve gotten is useful to you, you should “accept” it by clicking on the checkmark to the left.  If you want more help, you’ll have to give us more, first.  Leave a bundle of small, unmarked bills under the … ***just kidding!***  Seriously, you might get more of a response if you give us a better idea of how your data are laid out.  You don’t need to post screenshots — in fact, it’s better if you don’t.  Just type in representations of your sheets, as was done [here](http://superuser.com/q/889201/150988) and [here](http://superuser.com/q/892744/150988).  … (Cont’d) – Scott - Слава Україні Jul 12 '15 at 16:51
  • (Cont’d)  … It doesn’t have to be your *real* data; it’s OK (and, again, preferable) to use tuxedos and shoes, and replace real names (if any) with fake ones (e.g., Andy, Betty, Carlos, etc…).  If your actual worksheets are 42 columns wide and 1700 rows high, cut them down to the bare minimum necessary to illustrate the complexities of your problem.  While [this](http://meta.stackexchange.com/q/22754/195817) and [this](http://stackoverflow.com/help/mcve) may not be directly relevant, they may give you some ideas regarding what you need to provide and what you don’t.  … (Cont’d) – Scott - Слава Україні Jul 12 '15 at 16:53
  • (Cont’d)  … Finally, it is not our mission to be a formula-writing service.  You may get help extending your formula to cover the cases that you need if you show us what you’ve written so far and explain why you’re having trouble finishing the job.  Good luck! – Scott - Слава Україні Jul 12 '15 at 16:55

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
  • 521
  • 3
  • 6