Pull in most recent form entry only


I am trying to add an indicator to use for a report to only recognize the most current form entry for a date range. Specifically, I am using a auto fill "created" column and the collect functionality as follows:

=IF(ISBLANK(Created@row), "", MAX(COLLECT(Created:Created, [Week Indicator]:[Week Indicator], "1")))

However, this isn't really the greatest way to do it since I have to do multiple checks.

Is it possible to create a formula where I can check for the Team member name, matching start date, and apply an indicator if the Created Date is the max value?

Below is a snip of the form I'm making. The column "Most Current View" is where the IF formula above currently sits, but it's not working right =)

Best Answer

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

    Ok. To check a box for the most recent entry for each person, here is what I suggest...

    Add an auto-number column. Formatting doesn't matter.

    Add a text/number column called "Row #" and enter the following as a column formula:

    =MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

    Add a checkbox column with the following as a column formula:

    =IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], [Team Member Name]:[Team Member name], [Team Member Name]@row)), 1)

    This will check the box for the most recent entry for each team member.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!