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.
Best Answer
I do appreciate the effort here to help me with creating a running inventory counter. I realized at some point I better take the initiative and delve into some script so as to create an 'action' on command customized to my needs.
The idea was to fill entries of a Packing Slip with item numbers and quantities of that item sold. Then on another sheet that kept track of inventory, the quantity of current items sold would deduct from the remaining inventory. So a script was needed. (a formula would otherwise always revert back with each new entry.)
Basically, on the Inventory sheet (which had columns of item numbers and starting inventory) I used an IfError function with Match and Index nestled to find and transfer the corresponding entries from the Packing Slip. From there it was a simple process of scripting to create a counter.
Probably preaching to the choir here, but if anyone is interested, I can furnish the procedure, which involved a combination Numbers functions and applescript.
I also got a little fancier and insert a chime sound on completion as a reminder that the current order deductions were made from inventory, along with a notification banner.