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

Options

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.

Thank you in advance for your help.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @William Tremblay

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @William Tremblay

    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

  • Thank you, Genevieve!

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!