Hi, I have a Safety Violation Sheet that uses Form Entry to record violations and assign a measured Probation Period in days. If there are multiple entries for an individual, the probation days should accumulate, at the same time the value reduces by (1) for each day that passes without incident.
I have (1) sheet that acts as a Name database for the company, and a column on that sheet that uses: =SUMIF({Safety Violation (Responses) Range 1}, [EMPLOYEE NAME]@row, {Safety Violation (Responses) Range 2}) to show the cumulative Probation Days accrued.
I also have a column on my Safety Violation Sheet that looks up the amount of days per violation entered: =VLOOKUP([Severity of Violation]@row, {SAFETY VIOLATION WORKSHEET Range 2}, 2, false)
and another column that reduces the Probation Period by (1) for each day that passes: =SUM(TODAY() - Timestamp@row) and =SUM([PROBATION END (HELPER)]@row - [PROBATION DAYS (HELPER)]@row)
Unfortunately the way I have the formula it reduces (1) day for each entry, so if John has (3) violations, (3) days are removed for each day that passes, and it should only reduce by (1) day for whatever has accumulated.
It seems I may need to move my Reduce by (1) day formula to my Name Database sheet, next to the the SUMIFS formula, but I'm having trouble with what that would look like.
I feel like there should be an easier way to do this...
Any help would be appreciated
Thanks!