Auto Assign Contact based on number of Open Tasks
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!
Answers

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))))
Dragfill this formula down your column for all your current rows. You will now have an autonumbering 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 autofill when new rows are created, as long as they are present in two rows above the new one, so these will autopopulate 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
Help Article Resources
Categories
Check out the Formula Handbook template!