Assign a reviewer to a task based on which reviewer has the fewest open tasks

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:

1. Checkbox indicating a task is complete (no longer open):
1. =IF(ISDATE([Executive Review Complete]@row), 1, 0)
2. Counting the number of open tasks:
1. =COUNTIFS([Executive Reviewer]:[Executive Reviewer], "=XYZ Person", [Complete Is a Date]:[Complete Is a Date], "=0")
3. Displaying the name of the Executive Reviewer if that has the lowest number of open cases out of three:
1. =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.

• Employee

You're close! Based on your final formula, it looks like you're returning one name and blank cells at the top of this helper column. Instead, you'll want to use a column formula to return the name with the lowest tasks throughout the entire column. This way you can use the value as a Condition in an automated workflow.

=INDEX([Exec Reviewer]:[Exec Reviewer], MATCH(MIN([Open Reviews]:[Open Reviews]), [Open Reviews]:[Open Reviews], 0))

This will find the Exec Reviewer that has the least number of Open Reviews and put their name in the whole column:

Then because this name will appear with any new row created, you can use it as a Condition within an Assign People workflow:

The Condition looks at the current, newly added row to see the value for that row. It won't be able to check one specific cell, which is why I adjusted your formula to be a column formula.

Let me know if this makes sense or if you need any further clarification!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Employee

You're close! Based on your final formula, it looks like you're returning one name and blank cells at the top of this helper column. Instead, you'll want to use a column formula to return the name with the lowest tasks throughout the entire column. This way you can use the value as a Condition in an automated workflow.

=INDEX([Exec Reviewer]:[Exec Reviewer], MATCH(MIN([Open Reviews]:[Open Reviews]), [Open Reviews]:[Open Reviews], 0))

This will find the Exec Reviewer that has the least number of Open Reviews and put their name in the whole column:

Then because this name will appear with any new row created, you can use it as a Condition within an Assign People workflow:

The Condition looks at the current, newly added row to see the value for that row. It won't be able to check one specific cell, which is why I adjusted your formula to be a column formula.

Let me know if this makes sense or if you need any further clarification!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Thank you, Genevieve!

My frustration was trying to target the one cell containing the next reviewer, and this solution will solve that problem.

• Employee

No problem at all! It took me a while to figure out as well. I'm glad this will work for you.