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
-
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
-
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!
-
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.
-
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
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Michelle BassonSmartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet
https://www.linkedin.com/in/michelle-basson/CERTIFIED SMARTSHEET PLATINUM PARTNER
www.projectplanservices.co.uk -
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
-
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 😀
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Michelle BassonSmartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet
https://www.linkedin.com/in/michelle-basson/CERTIFIED SMARTSHEET PLATINUM PARTNER
www.projectplanservices.co.uk -
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!