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

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

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?

Kelly

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.