Automatic Lead Distribution

We are building sales app.

We are looking for automatic lead distribution. We have sales team of 50 people. Goal is whenever there is new lead in the spreadsheet it should be automatically allocated to executives one after another.

Probably a logic with a script like this would work. Let’s say you have a “Leads” sheet and an “Executives” sheet.

In the “Leads” sheet you have an Executive column to assign the Executive.

In the “Executives” you have a “Name” column, an “Order” column and an “Increment” column.

The logic for the “Increment” column, for an executive of let’s say 20 people would be: when “Order” equals 1, “Increment” = Max order number - 1, so it’s 19 in this case. In other cases of “Order”, the increment will be set to -1 (minus 1).

Now what we wanna do is when there is a new row in “Lead” sheet, go to “Executives” sheet, find the row with “Order” = 1, get the “Name” of the executive and set that value to the “Executive” column in the “Lead” sheet.

Go back to the “Executives” sheet, make an addition between the “Order” column and the “Increment” column. Now the executive with order 1, who has just been assigned a lead, will have a new order of 1 + 19 = 20, other executives will have their order number reduced by 1, i.e 2 -> 1, 3 -> 2 etc. The “Increment” column will be automatically updated to prepare for the next iteration.

Will you be able to help us implementing this in our sheet if we give access?

Sorry I probably won’t have time for that so please try it and let me know the results, I will try to follow when I am free.