I am trying to assign a reviewer to a task based on which reviewer has the fewest open tasks.
I have the following formulas working, and trying to hopefully use them together for automating the auto assign function:
- Checkbox indicating a task is complete (no longer open):
- =IF(ISDATE([Executive Review Complete]@row), 1, 0)
- Counting the number of open tasks:
- =COUNTIFS([Executive Reviewer]:[Executive Reviewer], "=XYZ Person", [Complete Is a Date]:[Complete Is a Date], "=0")
- Displaying the name of the Executive Reviewer if that has the lowest number of open cases out of three:
- =IF(AND([Open Reviews]@row < [Open Reviews]31, [Open Reviews]@row < [Open Reviews]32), [Exec Reviewer]@row, " ")
- Note: the above Exec Reviewer is a separate column from Executive Reviewer, and is a helper column for this purpose.
I am stuck here. My end goal is to assign the next submitted row (from an associated form) to the Exec Reviewer with the least open tasks.
Thank you in advance for your help.