Auto-assign task to next person on Contact list

Margaret Griffin

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?

Paul Newcome

You definitely do NOT want it to be a field on the form. When a form is submitted it overrides the formula with a blank which will break the auto-fill feature.

Paul Newcome

Have you tried using an IF statement around the assignment formula?

=IF(OR([Date Validation Needed]@row = "Yellow", [Date Validation Needed]@row = "Green"), assignment_formula, "")

Paul 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.

Margaret Griffin

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 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?

Margaret Griffin

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}}, Specialist@row)

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 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}}, Specialist@row)

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

Screenshot example for C.:

Margaret Griffin

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)

Paul Newcome

You definitely do NOT want it to be a field on the form. When a form is submitted it overrides the formula with a blank which will break the auto-fill feature.

Margaret Griffin

This auto-assign formula has been working great: =IF(AND([Assigned to]@row <> "", [Any Other Column]@row <> "Formatting Row"), 1). The way it's designed, it moves each new submission from the underlying sheet (with the auto-assign formula) to a working sheet where a 3rd sheet is referenced to determine the assignment.

On the underlying sheet, I also have a formula that validates the 'Due Date' field selected when someone submits a form. (We ask people to select a Due Dat at least 10 business days from the date they're submitting their request. But since there is no way to enforce this within the form field, we have a formula that sends an Update Request whenever a submission is received with an insufficient Due Date.) Here is the 'Date Validation' formula, which is coded for RYG balls: =IF(OR(NETWORKDAY(Created1, [Date Needed]1) < 10), "Red", IF(AND(NETWORKDAY(Created1, [Date Needed]1) = 10), "Yellow", IF(AND(NETWORKDAY(Created1, [Date Needed]1) > 10), "Green")))

So here's my question: Is there a way to tie the 'Date Validation' column to the auto-assign formula? In other words, would it be possible to require the 'Date Validation' formula to have a value of Green or Yellow before the auto-assign formula is applied to the submission? 

Paul Newcome

@mgriffin Are you able to provide a screenshot that shows what you are trying to accomplish? Manually entered data to show the end result along with some notes to explain the logic behind the end result would be very helpful.

Margaret Griffin

Current process: Each form submission is automatically assigned and moved to a working sheet.

1. Request form is submitted and Due Date is selected

2. Request is assigned to a specialist based on a formula in the underlying sheet: =IF(AND([Assigned to]@row <> "", [Any Other Column]@row <> "Formatting Row"), 1)

3. in conjunction with a formula in a helper sheet: =COUNTIFS({Working Sheet Range 1}, [Assigned to]@row)

4. Request is automatically moved to working sheet

BACKGROUND: On the Request form, we ask submitters to select a Due Dat at least 10 business days from the date they're submitting their request. Since there is no way to enforce this within the form itself, we have a formula in the underlying sheet that sends an Update Request whenever a submission is received with a Due Date less than 10 business days from the submission date. The Update Request is triggered when the RYG value in the 'Date Validation' field is Red. The formula for this field is =IF(OR(NETWORKDAY(Created1, [Date Needed]1) < 10), "Red", IF(AND(NETWORKDAY(Created1, [Date Needed]1) = 10), "Yellow", IF(AND(NETWORKDAY(Created1, [Date Needed]1) > 10), "Green")))

Ideal process: Each form submission must meet the date requirements before it is automatically assigned and moved to a working sheet

1. Request form is submitted and Due Date is selected

1A. Date Validation formula is applied. A 'Red' value triggers an Update Request. Only when the Date Validation value is 'Yellow' or 'Green' do the next steps occur.

2. Request is assigned to a specialist based on a formula in the underlying sheet: =IF(AND([Assigned to]@row <> "", [Any Other Column]@row <> "Formatting Row"), 1)

3. in conjunction with a formula in a helper sheet: =COUNTIFS({Working Sheet Range 1}, [Assigned to]@row)

4. Request is automatically moved to working sheet

ESSENTIALLY, I'm trying to introduce step 1a to our process; making the Auto-assign formulas contingent on whether the Date Validation formula returns a 'Yellow' or 'Green' value. I don't want the request to be assigned until the requester has selected an acceptable due date for their project.

Paul Newcome

Have you tried using an IF statement around the assignment formula?

=IF(OR([Date Validation Needed]@row = "Yellow", [Date Validation Needed]@row = "Green"), assignment_formula, "")

Margaret Griffin

The Assignment formula is also an IF statement. I'm not sure how to construct the syntax for nested IF statements, if that's even possible.

The syntax for the Assignment formula is

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

Placing that into the formula you suggested looks like this

=IF(OR([Date Validation]@row = "Yellow", [Date Validation]@row = "Green"), IF(AND([Assigned to]@row <> "", [Formatting Column]@row <> "Formatting Row"), 1), "")

That syntax returns an #UNPARSEABLE error, so I removed the second 'IF(AND

=IF(OR([Date Validation]@row = "Yellow", [Date Validation]@row = "Green"), ([Assigned to]@row <> "", [Formatting Column]@row <> "Formatting Row"), 1), ""))

Unfortunately that syntax also returns an #UNPARSEABLE error. Any ideas?

Paul Newcome

Start by double checking the column names to ensure they are correct. If they are correct, are you able to provide a screenshot of the first attempt (with the AND function) actually being used in the sheet similar to the screenshot below?

Margaret Griffin

Paul - While I was prepping the screenshot, I reapplied the formula and it worked! Here is the working formula, for future reference:

=IF(OR([Date Needed Validation]@row = "Yellow", [Date Needed Validation]@row = "Green"), IF(AND([Assigned to]@row <> "", [Formatting Column]@row <> "Formatting Row"), 1), "")

This allows us to automatically validate the due date before automatically assigning the request to an analyst. Fantastic! Thanks so much for all of your help.

Chris Van Nostrand

Hi, I am looking to do something similar, but for a ticket tracker for Operations team. Currently, after ticket form is submitted, I will manually assign the ticket out or have a ticket automatically assigned to an individual who is dedicated to that area (i.e. Payroll). Ideally, we would like to have any tickets that come in, assigned to our team by auto-assigning to the next team member in list and so on. I tried using the instructions above, but running into issues with how our tracker sheet was constructed. Would love to bounce some thoughts off of someone or perhaps there is another post I can reference. Thanks in advance!