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.

  • 1
    For us to help you it'll be good to know what formula you're currently using and what others you've tried? Also, a rough idea of how you've set it out may be good too (e.g. What cell the formula is in and any other cells/formulas that are relevant to the calculation). You can edit your question via the edit link below it. – Monomeeth May 6 '17 at 5:33
  • Zeroing out the packing slip removes the previous amount. Therefore your inventory returns to it's original amount. As @Monomeeth says, it would be helpful to know how your formulas are structured. Please edit your question with more details and what you have tried to do to save the original result. Cheers – bjbk May 8 '17 at 14:27
  • Well thanks. The only formula I know that would have a chance is the SUM. I looked thru them all, and it appears nothing would work from what I can understand. That's because formulas only compute existing data input, whereas I am looking for a performance that would serve like an action. That is, a one time command that performs a SUM formula, but retains the new result (of the inventory count), as the source cell is zeroed out. – MMM May 8 '17 at 19:19
  • Oops, I pressed – MMM May 8 '17 at 20:19
  • What you want to do will be possible, but we really need to know the formula you're using and the cells involved in the calculation. Otherwise anything we offer may not apply to your spreadsheet. For starters, explaining your 2nd paragraph in detail would help. Or perhaps you could share your file somehow for us to look at? Otherwise the only hint we can give you is that the problem, as @bjbk says, is that you're zeroing out the current total. So, you need to store this value or account for it somehow. – Monomeeth May 8 '17 at 20:35

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.

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .