Reporting most recent inventory number

I'm creating a dashboard for my clinical leadership so that they can see how much PPE staff is reporting is available onsite. I've created a form that staff will use to report current stock numbers but I am stuck on how to have the most recent reported number be reflected on a widget in a dashboard?


Staff will be reporting new numbers Bi-Weekly and leadership only wants to see the most recent number. Anybody have some suggestions on how to display this?

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭

    Sounds like you need a Max(Collect....) formula.

    I use a helper checkbox column with the formula below

    =IF([Date]@row = MAX(COLLECT([Date]:[Date], [Item Number]:[Item Number], [Item Number]@row)), 1)

    This flags the newest entry for that item number.


  • Jeff M.
    Jeff M. ✭✭✭

    Create a column named "Last Inventory Date" and plug this formula in.

    =MAX(COLLECT([Inventory Date]:[Inventory Date], [Staff Name]:[Staff Name], [Staff Name]@row))

    You can use locations instead of staff names if they are reporting inventories from different locations.

    Create a new column named something like "Last Reported Inventory", this will be where the newest number is populated. Then plug this formula in that column.

    =INDEX(COLLECT(Total:Total, [Inventory Date]:[Inventory Date], Last Inventory Date@row), 1)

    Total Column = the inventory number column on the form that the staff is reporting.

    You can add a "metric" widget to the dashboard and only include the cells that are updated.

    Example: First two rows

    Staff - A / Last Reported Inventory: 87 / Last Inventory Date: 01/30/22

    Staff - B / Reported Inventory: 75 / Last Inventory Date: 01/29/22

    The numbers and dates will change as forms are completed and rows are added below.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!