Auto-assign task to next person on Contact list

Auto-assign task to next person on Contact list

I need to auto-assign tasks in a round robin style (i.e. the assignment goes to the next person 'in line' in the contact list). Is there a way to do this with a formula? I understand that tasks can be auto-assigned based on criteria in a specific column but, in this case, the assignment would depend on who was assigned to the previous task. Any ideas?

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Are you able to provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed? If you are able to manually "mock up" what you are trying to automate, that would be very helpful as well. I can think of a few different possible solutions off the top of my head, but it is going to depend on the exact layout of your data.

  • I'm building the sheet/form from scratch and haven't input any dummy data input yet. But the project is essentially a tracking queue for report requests. Each request is submitted by a third party via the form. Upon submission, it needs to be auto-assigned to one of our four BI specialists a systematic order.

    Request 1 - Assigned to BI Specialist #1

    Request 2 - Assigned to BI Specialist #2

    Request 3 - Assigned to BI Specialist #3

    Request 4 - Assigned to BI Specialist #4

    Request 5 - Assigned to BI Specialist #1

    Request 6 - Assigned to BI Specialist #2

    Request 7 - Assigned to BI Specialist #3

    Request 8 - Assigned to BI Specialist #4

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I would create 2 more sheets.

    One sheet (Assignment Sheet) would need two columns. One for the Assigned to and another for a formula we will plug in shortly.

    The other sheet will be an exact replica of the form entry sheet (you can "Save as New") and just delete the form from this second one. We can call this the Working Sheet.

    Now we can go back to the Form Sheet. We will add in a checkbox column and enter the formula:

    =IF(AND([Assigned to]@row <> "", [Any Other Column]@row <> "Formatting Row"), 1)

    This will check the box for rows where the [Assigned to] column has been populated by something and [Any Other Column] is not "Formatting Row".

    Manually enter "Formatting Row" in the first two rows of the [Any Other Column] column.

    We also want to put the following into the [Assigned to] column:

    =INDEX({Assignment Sheet Specialist Column}, MATCH(MIN({Assignment Sheet Other Column}), {Assignment Sheet Other Column}, 0))

    Make sure that IF formula and the [Assigned to] formula from above are both also in those first two rows. These boxes will not be checked, but having the first two rows populated will allow autofill to grab these formulas for new form entries.

    The last thing we will do on the Form Sheet is to set up a Move Row Automation. The trigger will be when that checkbox is checked, and we are going to send it to the Working Sheet.

    Finally we can go back to our Assignment Sheet and plug that formula into the second column.

    =COUNTIFS({Working Sheet Assigned To Column}, [Specialist Column]@row)


    Now that we have everything set up, here is how it works...

    Form is submitted.

    [Assigned to] finds the lowest count on the {Assignment Sheet} and populates the name.

    The name being populated will check the box which triggers the Move Row Automation.

    The Move Row Automation grabs the form submission now that it has been assigned and moves it over to the {Working Sheet}.

    Now that the row is on the {Working Sheet}, it will update the count for whoever it just assigned and they will no longer be the lowest number in the {Assignment Sheet}.


    The reason for having the Move Row Automation is because the formula in the {Form Sheet} will update EVERY row with the person who has the least number assigned. The Move Row Automation captures the data as STATIC so it will not change for that particular row once it has been assigned.


    Does all of that make sense?

  • I see what you're getting at, but I'm getting stuck on some of the column references.

    Is [Specialist Column] and (Assignment Sheet Specialist Column) the 'Assigned to' column you mention in the 2nd sentence? ("One sheet (Assignment Sheet) would need two columns. One for the Assigned to and another for a formula we will plug in shortly.")

    On the Form sheet, for the [Any Other Column] column, I chose our existing 'Category' column - which is a drop-down column. I then created an [Assignment Formula] check box column and entered the following formula, which returns a value of #BLOCKED: 

    =IF(AND([Assigned To]@row <> "", [Category]@row <> "Formatting Row"), 1)

    In the [Assigned To] column on the Form sheet I entered the following formula, which resturns a value of #UNPARESEABLE:

    =INDEX({Assigned To}, MATCH(MIN({Category}), {Category}, 0))

    And in the 'Forumla' column on the Assignment Sheet, I entered the following formula, which returns a value of #UNPARESEABLE:

    =COUNTIFS({{Working Sheet Range 1}}, [email protected])

    On the Form sheet, the [Assignement Formula] checkbox is checked when a value is selected from the [Assigned To] contact list. And I was able to get the Move Row automation to work. But I'm obviously missing some key details. Any ideas?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    A. Is [Specialist Column] and (Assignment Sheet Specialist Column) the 'Assigned to' column you mention in the 2nd sentence?

    That is correct.


    B. On the Form sheet, for the [Any Other Column] column, I chose our existing 'Category' column - which is a drop-down column. I then created an [Assignment Formula] check box column and entered the following formula, which returns a value of #BLOCKED: 

    =IF(AND([Assigned To]@row <> "", [Category]@row <> "Formatting Row"), 1)

    What is in the Category column?


    C. In the [Assigned To] column on the Form sheet I entered the following formula, which resturns a value of #UNPARESEABLE:

    =INDEX({Assigned To}, MATCH(MIN({Category}), {Category}, 0))

    I don't see any syntax issues. Can you provide a screenshot of the formula in the sheet similar to the screenshot at the bottom of this comment?


    D. And in the 'Forumla' column on the Assignment Sheet, I entered the following formula, which returns a value of #UNPARESEABLE:

    =COUNTIFS({{Working Sheet Range 1}}, [email protected])

    You have an extra set of curly brackets around the cross sheet reference. It should only be a single set.


    Screenshot example for C.:


  • Everything is working, except I can't get the checkbox in the Formula column on the Assignment sheet to populate when a new form is submitted. I'm not getting any errors with the formula. And when I manually check the box, it initiates all the other for

    mulas. I even added the 'Formula' field to the form as a hidden field, but I still couldn't get it to populate with new submissions. Here it the syntax and a screenshot:

    =IF(AND([Assigned to]@row <> "", [Any Other Column]@row <> "Formatting Row"), 1)

Sign In or Register to comment.