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:
- 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.
Best Answer
-
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.
I would adjust your final formula to be this, instead:
=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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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.
I would adjust your final formula to be this, instead:
=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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you, Genevieve!
My frustration was trying to target the one cell containing the next reviewer, and this solution will solve that problem.
-
No problem at all! It took me a while to figure out as well. I'm glad this will work for you.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!