I want to create a very basic stock management sheet. adding in stock updates quantity and

Options

removing stock updates the quantity. I cannot figure out what the easiest method would be without creating a loop because if I add stock I would have to keep upping the numbers of that column.

If I already had a quantity of 20 water bottles and then I got in another 20 bottles of water one week, and the next week I got another 20, instead of just putting in 20, I would have to change the existing 20 to 40 bottles of water for a total of 60.

same thing goes for the removing stock. If I had 60, but I got rid of 20 one day, then the next another 20 I would essentially be doing the subtraction myself.

Is there any way of changing this so those Columns could go back to 0 as soon as the total quantity is updated?

this is a very bare bones sheet right now as I try to come up with a system.

Is it worth the headaches of just updating the numbers myself manually?

Answers

  • Purnima Gore_PGPS
    Purnima Gore_PGPS ✭✭✭✭✭
    Options

    Hi @santiago.rendon

    have you tried using the summary functionality in the side bar?

    Then use the Sheet Summary Field to have the quantity update

    You could do this differently adding each line item each week if that is what you want and then using the same with a sumif, formula and then pulling this into other fields so that you don't have to do this manually.

    Let me know if this works. This is the simplest way based on your screenshot.

    Thanks

    Purnima

  • santiago.rendon
    Options

    Hi, @Purnima Gore_PGPS thank you for your answer. It does seem like this is the easiest way to do it. Based on my screenshot I made the sheet as barebones just to test out the functions. There would be roughly around 20 different lines to keep track of which I think would make the weekly thing a bit confusing but I am trying to picture it

    Could you show me via screenshot what you mean by adding it weekly?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!