Calculating a Running Balance

Eric Sagong

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

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

Eric Sagong

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å

Thanks!

Happy to help!

Eric Sagong

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!

Eric Sagong

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å

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!

Michelle Basson

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

Eric Sagong

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

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 😀

Eric Sagong

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