Formula to auto assign based on request number series?

JB MacPhee

Is there a formula that could be used to assign staff automatically based on request number series? The sequential request number will be auto generated when the form is submitted.

For example, if one location has three staff that get assigned requests in the order they are received, I would like to automate it so that:

• staff person number one (SP1@sample.com) will be assigned request numbers 1, 4, 7, 10, 13...
• staff person number two (SP2@sample.com) will be assigned request numbers 2, 5, 8, 11, 14...
• staff person number three (SP3@sample.com) will be assigned request numbers 3, 6, 9, 12, 15....

I was just asked to put together the forms/sheets for multiple locations (with anywhere from 3-10 assignees) and this was one of the requirements. Any input or advice is very much appreciated!

Thank you for your time!

Jim

Mike TV

@JB MacPhee

One method would be to create a separate sheet that each request is copied to. Then you can do a MAX(COLLECT formula to see which one of your staff person has the most assigned requests. From there knowing that you can create an IF formula on your main sheet which checks to see who has the most # of requests assigned. If it's person #1 you write the result to be person #2. Then you nest another IF inside the formula for if the MAX(COLLECT is person #2 that it assigns to person #3. Then you nest another IF inside the formula for if the MAX(COLLECT is person #3 that it assigns to person #1.

If you're starting with an empty sheet you'll get an error so you can add to the front an IFERROR that results to person #1 if there's an error, to give a starting point.

JB MacPhee

Makes sense; thank you very much Mike!

My only concern/question is when a new person comes on, I would expect that they would be assigned quite a large number of requests until they no longer had the fewest assigned (staff changes really throw a wrench in my best laid plans :).

Mike TV

@JB MacPhee

Yes, that would happen. It would keep assigning to the same person until they were finally the one with the most assigned to them. Probably not what you'd want to happen to a brand new person unless they already knew the job.

To avoid that, you could add some dummy rows to the copy to sheet with their name in it so the formula thinks they have things assigned to them. The dummy rows should be easy to locate because you'd add a batch of them all together and they'd be devoid of the other information that would have been copied over from the main sheet if they were real assignments. Once they're up to speed you can delete the dummy rows (maybe a little at a time).