Reporting most recent inventory number

Gabe Tarin

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?

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.

https://help.smartsheet.com/function/max

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.