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!
SSFeatures - The browser extension that adds more features into SmartSheet.
- Automatic sorting, sorting with filters, saving sort settings
- Spell checking
- Report PDF generator that supports grouped and summarized reports
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!