Calculating a Running Balance

Hello, everyone! I'm currently working on an inventory system that is linked to one "Master Inventory" sheet. I don't know how to best create a "running balance" so that every time an item is scanned and an order is generated, the item's "Master Inventory Record" is deleted accordingly.

Here is an example of the "Master List"

And here is the "Orders List"

Basically, what I'm trying to do is have the running balance show up on one single line and update every time a new order is placed. For example, "Small Pink Shirts" has two orders, so ideally the "Small Pink Shirts" line of the "Master List" would be depleted from the total stock and the "Running Balance" figure would update automatically. (i.e. depleted by 100 shirts and then again by 100 shirts once it is scanned again, as shown in the example above).

Any help anyone could give would be greatly appreciated. Thank you!

Regards,

Eric

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Eric,

    I’ve built something similar for a client recently.

    I’ll get back to the post later.

    In the meanwhile, it would make it easier to see how it would fit with your solution if you maybe could share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andree,

    Thank you so much for your quick response! Per your request, I have shared those two sheets with you.

    Regards,

    Eric

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Thanks!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • I've tried using the following SUMIF forumula:

    =SUMIFS({Vertiv Reynosa Supermarket Range 1} = SKU3, {Vertiv Reynosa Supermarket Range 3})

    And I am getting an #INCORRECT ARGUMENT error. Can you tell me if I'm at least on the right track? Thanks!

  • Hello, everyone! I figured it out, I needed a "comma" instead of an "=" between the "{Vertiv Reynosa Supermarket Range 1}" and "SKU3". Thank you, Andree, for your willingness to take a look at this for me!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Excellent! 

    Glad you got it working! 

    Best, 

    Andrée

    Please help the Community by marking your post with the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Michelle Basson
    Michelle Basson Overachievers Alumni

    Hi,

    I am reading through this thread. I am also currently busy with something similar.

    I have techs who put in a stock request for items they need for the day/week. I then need to log certain items to various techs. The problem is, these are items that are serialized and other that are non-serialized. I can request 10 Grids and I can deduct the grids from the total in stock, I however need a way to allocate specific grids with their unique Serial number (SN) to a tech. This item with its' SN then needs to be removed from my main stock status / stock on hand sheet.

    Any recommendations on tracking the serial numbers between sheets and still deducting the units required from the main stock count?


    Kind regards

    Michelle

    Michelle Basson

    Smartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet

    https://www.linkedin.com/in/michelle-basson/

  • Michelle,

    I think the best thing to do would be to have a separate sheet for each technician that they scan into that depletes stock from a "main" sheet. That's what I have set up for myself. Please let me know if this doesn't answer your question/you need additional guidance.

    Regards,

    Eric

  • Michelle Basson
    Michelle Basson Overachievers Alumni

    Hi Eric,

    I currently have separate sheets for the technicians, but they are filled by the rows that move from the main stock sheet (automation for row move)

    I could let the stock controller add the totals supplied to each individual tech sheet, and do a standard subtraction formula to remove the amount allocated to the tech from the cumulative total. This would however not remove the item from the main input stock sheet, and that sheet would keep growing.

    I need some wat of moving the item from the main stock sheet. but when the input is done, the non-serialized items can be about 500 units that come in at once. and if the tech requests 20 units, the entire row will be moved.

    taking the 500 units out of the stock on hand sheet.


    Hope this makes sense.

    Any advice will be appreciated.


    Regards

    Michelle 😀

    Michelle Basson

    Smartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet

    https://www.linkedin.com/in/michelle-basson/

  • Michelle, sorry for the delayed response, but I'm still not quite sure what it is you're trying to do. If you'd like to message me privately, I'd be happy to speak to you about this in greater detail. I can be reached at sailorjerry431@gmail.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!