Auto Assign Contact based on number of Open Tasks

Christina Boyce

Any help would be great. I have a form that dumps information into my sheet and would like to auto assign the task to a person/contact when it comes in.

The Assignment is on a rolling basis. For example I have 4 people and as the work comes in it gets assigned to each one then starts again at the top of the list.

Has anyone done this?

Thanks!

Genevieve P.

Hi Christina,

There isn't a specific function that would automatically fill this information in on a rolling basis, but we may be able to create something similar with helper columns.

You could first create a new text/number column that will be your numbering system (I called mine "Row Number"). In this column, enter "1" into the first cell. Then put this formula into the second cell:

=IF([Row Number]1 = 1, 2, IF([Row Number]1 = 2, 3, IF([Row Number]1 = 3, 4, IF([Row Number]1 = 4, 1))))

Drag-fill this formula down your column for all your current rows. You will now have an auto-numbering column that will repeat back to 1 after reaching 4. I would suggest hiding this column so no one adjusts the formula.

Then in your contact column, you will need to assign each one of your 4 users to a number, like so:

=IF([Row Number]@row = 1, "[email protected]", IF([Row Number]@row = 2, "[email protected]".... etc

Formulas will auto-fill when new rows are created, as long as they are present in two rows above the new one, so these will auto-populate when a form is submitted. (See here)

Another alternative would be to use a COUNTIF formula to count each person's total rows, and manually assign the task to the person with the lowest number.

If you're having trouble with either of the formulas, please provide a screen capture and I'm happy to help!

Cheers,

Genevieve