Return value based on a MAX date and other criteria

Me again

Is it possible to create a summary report that shows the individual rows of data based on the latest date (Date of Completion) and other columns so as to only show 1 row per location (multiple locations can occur in a single building) - See example image below

The items in green have the latest dates against their respective locations and would pull through from the Source Data (top) to the Summary Report (bottom)

Any ideas?

Thanks

Dan

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You can filter on multiple columns (up to 3). If you wanted a report, you could inset a checkbox column and use something along the lines of...


    =IF([Date of Checks]@row = MAX(COLLECT([Date of Checks]:[Date of Checks], Building:Building, Building@row, Location:Location, Location@row)), 1)


    This will check the box on every row for the most recent update for that particular building/location combo.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!