How to flag a row if a checkbox is unchecked for more than 2 weeks in a row

Kishan Naik RA

My team uses a hierarchical sheet to track weekly student attendance by checking the 'Check In Data' box if a student is present. I want to create a column or formula that will flag a row if the 'Check in Data' box is unchecked for TWO WEEKS in a row. I can easily create a flag column that inserts a flag if that week's checkbox is not checked but I am struggling to find a way to read two weeks (or any time span for that matter) and only flag those that have multiple weeks in a row unchecked. I have attached the spreadsheet below to give a visual of the spreadsheet and what I am trying to do.

Kishan

Jeff Reisman

My first approach would be to use a helper column containing the week number. This "WeekNum" column should be populated for each student for each week.

Add another helper column called "Unique" to create a unique value with the student's name and the week number:

=Student@row +"-"+ WeekNum@row

Add a checkbox helper column called "LastCheckIn" to pull the previous week's Check In Data field for that student:

=INDEX([Check In Data]:[Check In Data], MATCH(Student@row + "-" + (WeekNum@row-1), [Unique]:[Unique], 0))

In your flag column, use an IF to figure out if this week and the past week are unchecked:

=IF([Check In Data]@row, 0, IF(LastCheckIn@row, 0, 1))

In English: If this week's check in data box is checked, no flag. If last week's check in box is checked, no flag. If both this week's and last week's check in box is unchecked, flag.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316640#Comment_316640

Should actually wrap your "LastCheckIn" and flag column formulas in IFERROR to prevent the #NO MATCH error:

=IFERROR(INDEX([Check In Data]:[Check In Data], MATCH(Student@row + "-" + (WeekNum@row-1), [Unique]:[Unique], 0)), "')

=IFERROR(IF([Check In Data]@row, 0, IF(LastCheckIn@row, 0, 1)), "")

Kishan Naik RA

Jeff,

Thank you for your help, I think I am 95% of the way there (the IFERROR addition to your comment definitely helped as I was getting NOMATCH at first). I have attached some screenshots to help show you what I am still having trouble with but essentially it looks like the INDEX formula in the LastWeekCheckIn column seems to be pulling from the row below the correct student.

The example in the screenshots show me unchecking Check In Data for Angel and Izzy in Week 1 but in the Week 2 LastWeekCheckIn column Izzy and Jacob are unchecked. The formula looks really close but just reading the wrong row and I can't seem to figure it out.

Jeff Reisman

@Kishan Naik RA

Can you share the exact formulas you are using in the Week 1 and Week2 LastWeekCheckin column for Angel, Izzy, and Jacob? I'd like to see the formulas for all 6 rows involved.

Kishan Naik RA

The formula used in the LastWeekCheckIn column for all three students is as follows:

=IFERROR(INDEX([Check In Data]:[Check In Data], MATCH(Student@row + "-" + (WeekNumber@row - 1), [Unique Tag]:[Unique Tag], 1)), "")

I just dragged that down for the following weeks so each row in that column has that same formula.

Jeff Reisman

I suspect this might be causing it to move a row down:

Change that to a zero and see if that doesn't correct your issue.

=IFERROR(INDEX([Check In Data]:[Check In Data], MATCH(Student@row + "-" + (WeekNumber@row - 1), [Unique Tag]:[Unique Tag], 0)), "")

Kishan Naik RA

=IFERROR(INDEX([Check In Data]:[Check In Data], MATCH(Student@row + "-" + (WeekNumber@row - 1), [Unique Tag]:[Unique Tag], 0)), "")

I used the formula above changing the 1 to 0 and all the checkboxes in LastWeekCheckIn were unchecked even though the previous week Check In Data is checked (screenshot above).

I also included a screenshot of the first half of the sheet which shows the hierarchy that we use to separate weeks. I am wondering if potentially the hierarchy is throwing the formula off (below)?

Thanks for the help Jeff, it is highly appreciated!

Jeff Reisman

@Kishan Naik RA

I'm wondering something here: why are your Unique Tag values showing up as "Izzy Stahl.2" for example, when the formula relies on the unique tag being "Student@row + "-" + Weeknumber@row) ? Shouldn't it show up as "Izzy Stahl-2"?

I don't think your hierarchy is causing issues. I set up a similar sheet to test with, and my formulas work right out of the gate. Here you see the flags are red for Jared and Jeff, because they are slackers who don't go to class:

LastWeekCheckIn checkbox formula:

=IFERROR(INDEX(CheckInData:CheckInData, MATCH(Student@row + "-" + (Weeknumber@row - 1), Unique:Unique, 0)), "")

For the Unique tag, I added this in to ignore the parent rows that have "Week 1" in them, etc., but even without excluding those rows, everything works.

=IF(LEFT(Student@row, 4) = "Week", "", Student@row + "-" + Weeknumber@row)

For the Flag column:

=IFERROR(IF(CheckInData@row, 0, IF(LastWeekCheckIn@row, 0, 1)), "")

When I change the CheckInData boxes, the flags and LastWeek boxes work as they should:

Kishan Naik RA

Ha well you found my mistake. For some reason my formula for the LastWeekCheckIn column was =Student@row + "." + WeekNumber@row

I had no idea the period vs dash made a difference in creating the unique tag but that fixed it! Thanks again for the help :)

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317704#Comment_317704

It's always that one little thing. Back in the 1970s, a programmer left a semicolon off a single line of code, and a probe going to study Mercury went off course by millions of miles. Glad your case was less catastrophic!

The issue here was that your formula was specifically looking for a value of StudentName-WeekNumber, not StudentName.WeekNumber, so the match couldn't be made and the system couldn't follow the rules.

=IFERROR(INDEX(CheckInData:CheckInData, MATCH(Student@row + "-" + (Weeknumber@row - 1), Unique:Unique, 0)), "")