Assigning agent to a row in "round robin"

PeterLatcapital

"I am using: "=IF"([Row ID]@row <> "", INDEX({People Sheet Range 1}, MATCH(MOD([Row ID]@row, MAX({People Sheet Range 1})) + 1, {People Sheet Range 1}, 0))) on my primary sheet to return an agent name in sequence from the secondary {people sheet}. I can't get the formula to return the name, I just get the sequence number. Also, when I change the name in the people sheet it updates all completed rows. I want to lock the row so that on future days when agents change I can have a different distribution of assignments and not affect the rows already assigned.

Jeff Reisman

@PeterLatcapital Yours is a complicated ask, but I think I can help you get close to there.

In a test sheet, I added a system Created column for the date, then a CreatedDate column where I take the date portion of the Created value only:

 =IFERROR(DATEONLY(Created@row), "")

Then I added an AssignedDate column. Ideally, the values in the CreatedDate and AssignedDate columns should be set the same by using something like Record a Date automation to record the current date in the AssignedDate column when a row is added.

Next I added a column called DailySequence. Provided your sheet is sorted by Created date ascending (oldest rows at the top,) the following formula will increment by one in sequence for each date:

=COUNTIFS(CreatedDate$1:CreatedDate@row, AssignedDate@row)

This says, "starting at the first row and ending at this row, count all the rows where CreatedDate is the same as the AssignedDate on this row."

Here you see they are incrementing for each day.

Depending on how many new rows you expect each day and how many agents you have, you would want your people sheet to cover those numbers with an agent name. Something like this:

Now you set your Assigned Agent using a simple INDEX/MATCH against your people sheet:

=IFERROR(INDEX({People Sheet Agent Name Range}, MATCH(DailySequence@row, {People Sheet Agent Number Range}, 0)), "")

In order to keep completed rows from reassigning when your people list changes, you'll want to set up automation to move completed rows off to another sheet. The cool thing is that when you move a row, the formulas don't move with it - only the value results of those formulas do. So if back in September Agent 1 was John Smith, but now Agent 1 is Steve Rogers, the moved row from September will always show John Smith, even as new rows show Steve Rogers.