Displaying Most Current Info

To Whom it May Concern:

Goal: Automate filtering data based on two column values. I want to display the most current record of a duplicate entry in a report/dashboard.

Problem: I do not know the best approach. I have setup a sheet to collect 7 pieces of information: Duplicate PPR, Employee ID (PPR), Last Name, First Name, Full Name, Current, and Submission Date. I have setup the Duplicate PPR column as formula controlled column. The applicable cell will trigger once it notices that there are duplicate entries in the Employee ID column; the Employee ID column is my primary column.

Duplicate PPR Column Formula: =IF(ISBLANK([Employee ID (PPR)]@row), 0, IF(COUNTIF([Employee ID (PPR)]:[Employee ID (PPR)], [Employee ID (PPR)]@row) > 1, 1))

What I would like to happen next is, using the current column, have a formula setup similarly to the Duplicate PPR Column to trigger when a new record entry is submitted but is a duplicate to one that already exists. The formula should use the duplicate column, Employee ID, and submission date to check the box in the current column. The formula will find any checked checkboxes for Dup. PPR and check the box for any record that has the most current date. Similar to what is shown below:

I have come with the following formula but I have not gotten it to work yet:

Current Column Formula: =IF(AND([Duplicate PPR]@row = 1, [Submission Date]@row > TODAY([Submission Date]@row)), 1)

I get the following from the cell:

Any help will be greatly appreciated!

Best Regards,



  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭

    Hi @LaBeach , if what you're considering as a "duplicate" is the person's Full Name, you can use the following formula which includes both MAX and COLLECT to gather the latest Submission Date and compare it to the current row. If it finds the latest is the same row, it checks the box. Otherwise, it leaves the box blank.

    This formula also accounts for different names in the Full Name column as shown below.

    =IF(MAX(COLLECT([Submission Date]:[Submission Date], [Full Name]:[Full Name], [Full Name]@row)) = [Submission Date]@row, 1, 0)

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!