Column Formula Help - Helper Checkbox for Shift Entries

Brett B.

Hello Community,

I am looking for some help with a helper column checkbox formula needed to support reporting of data to a dashboard and downstream formulas.

I have four shifts (A,B,C,D) entering data points into a sheet, with the potential for multiple line entries occurring per shift per day worked (see example below).

I need the most recent shift's entries and the previous shift's entries to be reported out to a dashboard and used in downstream formulas. To do this, I have created a checkbox helper column (Shift for Reporting) but I am struggling to find a formula that will check the most recent shift's lines, as well as the previous shifts lines and update when lines are entered. I am not sure if it is relevant (likely dependent upon the functions used), but sequencing of shift entries will include all of the following:

A follows B, B follows A, B follows C, C follows B, C follows D, D follows C, or D follows A.

Any help or suggested formulas are greatly appreciated as I have it a wall with this one. Thank you!

Melissa Yamada

Hello @Brett B.

A possible solution is adding 2 helper columns:

1. Row ID - auto-number column
2. Most Recent Entry - text/number column

Then:

Step 1: Add most recent entry formula in the Most Recent Entry helper column

=MAX(COLLECT([Row ID]:[Row ID], [Shift Name]:[Shift Name], [Shift Name]@row))

Step 2: Add this formula on your checkbox column:

=IF(OR(MAX(COLLECT([Row ID]:[Row ID], [Shift Name]:[Shift Name], [Shift Name]@row)) = [Row ID]@row, MAX(COLLECT([Row ID]:[Row ID], [Row ID]:[Row ID], @cell < [Most Recent Entry]@row, [Shift Name]:[Shift Name], [Shift Name]@row)) = [Row ID]@row), 1, 0)