Reporting Most Recent Grid Entry on Report Based on Values in 2 Different Columns

Biiams

I have created a dash board where I want to show a live inventory count on each chemical size we have on hand. I have set my grid up to have a "Qty Remaining" column where the most recent entry of each chemical / size displayed is the live count for that chemical size. But now I want my dashboard to show the last entry / most up to date count vs showing all of them. For example the Alpet D2 Sanitizer 1 gallon - qty 4 is the most recent count since the 2/8/24 entry removed 6 from inventory. How do I get my dashboard to only show 4 vs both line items for when 10 were entered into inventory on 2/6 AND 4 when 6 were removed on 2/8?

This grid is tracking 10 chemicals that come in multiple sizes so I am guessing the formula will need to look at chemical name and container size so it is pulling the latest entry for each chemical size we have for all our chemicals.

JamesB

@Biiams

I think this will do what you need.

=SUMIFS(QTY:QTY, Chemical:Chemical, ="Alpet D2 Sanitizer", [In/Out]:[In/Out], ="In", [Container Size]:[Container Size], ="1 Gallon") - SUMIFS(QTY:QTY, Chemical:Chemical, ="Alpet D2 Sanitizer", [In/Out]:[In/Out], ="Out", [Container Size]:[Container Size], ="1 Gallon")

You could use your sheet summary area to Create a field named "Chemical - Size" <---Use actual chemical names and sizes in the field name and then adjust the formula above as needed for each of the different chemicals. From there you can create a report from the summary for your dashboard. Or you could use a metric sheet and use cross reference formulas to gather this data if it is a lot of different chemicals and sizes.

JamesB

@Biiams

I think this will do what you need.

=SUMIFS(QTY:QTY, Chemical:Chemical, ="Alpet D2 Sanitizer", [In/Out]:[In/Out], ="In", [Container Size]:[Container Size], ="1 Gallon") - SUMIFS(QTY:QTY, Chemical:Chemical, ="Alpet D2 Sanitizer", [In/Out]:[In/Out], ="Out", [Container Size]:[Container Size], ="1 Gallon")

You could use your sheet summary area to Create a field named "Chemical - Size" <---Use actual chemical names and sizes in the field name and then adjust the formula above as needed for each of the different chemicals. From there you can create a report from the summary for your dashboard. Or you could use a metric sheet and use cross reference formulas to gather this data if it is a lot of different chemicals and sizes.

Biiams

@JamesB

Thanks James, I was able to implement this and will try it out over the next month or so, so far it appears to do what I need it to!