Formula to tick a checkbox if the checkbox in the following row is ticked

OK - so I have a sheet that gathers weekly report commentary and I need to generate a report that shows the latest commentary, and the previous commentary entry.

Have a helper column with the created date in to tick an 'isLatest' checkbox for the latest entry - =IF(Created@row = MAX(Created:Created), 1, 0)

What I now need to do is create a formula that ticks a column 'isPrevious', but only for the row before the ticked 'isLatest' row.

So the problem is "tick the 'isPrevious' column for the one row that has 'isLatest' ticked in a following row"

So far I've been able to tick all the 'isPrevious' rows - but that doesn't move me forwards :)

Many thanks in advance for any help


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 09/19/22

    Hey @Snaillybob

    Try this

    =IF(Created@row = MAX(COLLECT(Created:Created, Created:Created, ISDATE(@cell), IsLatest:IsLatest, 0)), 1)

    This finds the max Unchecked IsLatest row, which should be the previous Max.

    Will it work for you?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!