Stocklist

TVP ALAN

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)

SSFeatures

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!