Stocklist
Hallo
i am looking for a tool to create a list into smartsheet where we can see the QTY of goods inside warehouse.
Via Forms i can make a scanningtool … which we do for 1 particulare client with al the same products. This works , today we have an other type of client in the warehouse with 5 different products. So i want to put all in smartsheet where we can see per product QTY in and OUT + dates and weeks so we can run a report what happen week X + stock overview per week as customer is paying per week
Thanks if someone can help me out , so i can stop the excel list :o) and i can start with scanning :o)
Answers
-
Hi,
You can do something like this:
- Each row is a product that was scanned for the week.
- Product is the name of the product that was scanned.
- Quantity is the amount of product that was in the warehouse at the time of scanning.
- Scanned Date is the date that the product was scanned.
- Week is a helper column that calculates which week this scan happened.
- Previous Week is a helper column that calculates the previous week for the previous scanned product.
- Quantity Changed calculates how much the quantity increased or decreased from the previous week.
This is the formula for Week:
=YEAR([Scanned Date]@row) + " " + WEEKNUMBER([Scanned Date]@row)
It combines the year and the week number
This is the formula for Previous Week:
=YEAR([Scanned Date]@row) + " " + VALUE(WEEKNUMBER([Scanned Date]@row) - 1)
This is the formula for Quantity Changed:
=Quantity@row - INDEX(COLLECT(Quantity:Quantity, Week:Week, [Previous Week]@row, Product:Product, Product@row), 1)
This formula subtracts the current quantity from the previous week's quantity for the given product.
There's one problem the Week and Previous Week formulas. Once the year switches to 2025 you'll need to manually update the formula. You can brainstorm a better formula that handles switching between years.
Hope this helps you on the right path!
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!