This must exist, but I can not figure it out with Numbers. I have an inventory of products I sell. I want to automatically subtract from the total original inventory each time an item sells. And to do this, I fill out a re-useable packing slip in Numbers. The item and quantity is listed on the packing slip, which in turn automatically deducts from the inventory- a simple sum formula.
For example, I have 200 of an item and in Numbers it is listed as such in a certain cell in a table that keeps track of inventory. If I sell 10 of that item to a new client, I fill out a packing slip. My formula in the packing slip automatically subtracts 10 of that item from inventory. Now the inventory states 190 left. Great so far.
I save a copy of that packing slip for the client as a pdf. And then later that same standard packing slip is zeroed out and used for the next client.
So say the next order is a dozen for that same item. Now a problem. The inventory formula only recognizes the latest entry, so instead of now having 178 items left in inventory (200-10-12=178), the inventory states 188 items left because it 'forgets' about the ten already sold.
I don't want to manually fill myriads of entries in cells that compute each and every transaction just to make this work. I just want to formulate a running inventory count so each time a packing slip entry is made, the corresponding amount is further subtracted from the ongoing inventory count, just like a barcode reader might subtract from the total as a counter.
Any ideas? Thanks in advance.