FIFO (First in first out) Inventory
We currently have two main sheets: one where we buy inventory stock (and the same material code may be entered on the sheet multiple times but with different dates) and then a second sheet for orders from customers. Once customers order, we need the inventory to deduct from our sheet from the OLDEST items first. For example, if we ordered the following for our stock: 20 units on 1/1/21, then 20 more units on 2/1/21 and another 20 on 3/1/21, and then a customer bought 30 units, we would need to see that all 20 on 1/1/21 were consumed and none are left, that 10 on 2/1 were bought and 10 are still remaining from 2/1 and then that none from 3/1 were consumed and all 20 from 3/1 are still remaining.
We currently have a formula set up in the first sheet but even though we have not maxed out smartsheet's requirements for columns/rows we actually are getting errors that we have too many formulas, so I am looking to see if anyone else has ideas.