Friday, October 26, 2012

yogi_Compute Running Total Of Inventory By IN OUT And Make Adjustment For Periodic Reconciliation

                                           Google Spreadsheet   Post  #830
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Oct 26, 2012
user Tim Hutton said: (!mydiscussions/docs/3W-wiOcLKtI)
Trying to create a "Stock Count" feature on an inventory spreadsheet.
We operate a Ink cartridge refill business and I am in the process of creating an inventory spreadsheet to keep a running total of what stock we have, in this case empty ink cartridges.
So far the spreadsheet calculates what stock comes in (empty cartridges) and what stock goes out (filled cartridges and failed cartridges) from a google form and displays the total in stock in a front-end table.

For example:
=if(Sheet2!$C2="Failed Cartridges Out",-sheet2!D2, if(Sheet2!$C2="Pre-Filled Cartridges Out",-sheet2!D2, if(Sheet2!$C2="Empty Stock In",sheet2!D2)))

What I would like to have is a stock count feature where one can simply count up the current stock of an item, input that figure into the form and the spreadsheet adjusts on the front-end table. One could simply have the user count the stock, refer to the table and input the difference making the formulae required simple, but that would defeat the point of having a spreadsheet to save the user time and effort.

At first glance it seems that when Sheet2!=$C2="Stock Count" the spreadsheet calculates the difference between the current total and the stock count total and inputs the difference as a correction. So that the front-end table sums up in the normal fashion.
Unfortunately with my limited knowledge of spreadsheets in general I always end up with a cyclic error, it may be I'm missing out on a trick somewhere or I need a script to do what I need or simply I'm looking at it all from the wrong way round.

Any help would be appreciated, I'm on a learning curve here.

Link to file:
following is a solution to the problem