In short, new rows are add to a sheet using a form, these currently get manually picked by the team and they assigned them selves that job.
However, due to cherry picking I want the system to auto allocate that new row to a name.
As the team changes daily, I want the auto name to be chosen from the names of the people who are in work just that day.
My thoughts. Sheet 1 - Main data (Where the new rows are added using forms)
Sheet 2 a list of advisors which gets manually changed each morning and then this list is used by the formula to allocate work the (row) work out that day.
I would like this to be in sequence.
Chris, Jon, Bob and repeats. (on each new row added 1 persons name is assigned)
Hey Chris
I meant to add commas instead of slashes in the MOD function. I guess my brain was still thinking division. Here's the correction.
=IF(MOD([Row ID]@row,3)=0, INDEX({People sheet Names column}, MATCH(3,{People sheet Order column},0)), IF(MOD([Row ID]@row,2)=0, INDEX({People sheet Names column}, MATCH(2,{People sheet Order column},0)), INDEX({People sheet Names column}, MATCH(1,{People sheet Order column},0))))
I think this will fix it.
Kelly
Hey Chris
There is a solution to your request, thanks to a community post by Bob Kahn
Row #1 of your intake sheet will always skip person #1 in the sequence, so the initial sequence on the Main sheet runs 2-whatever. All subsequent sequences follow the desired pattern 1-whatever. Each day when you update the People sheet, you will now also need to update the Order column to match the number of people in the list. Always start with #1 in your sequence list. You must have a #1. It should only be the one time on the sheet that the sequence skips a person.
=IF([Row ID]@row <> "", INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(MOD([Row ID]@row, MAX({Re-Delivery Team - Allocator Range 2})) + 1, {Re-Delivery Team - Allocator Range 2}, 0)))
The IF in the formula just keeps the Name blank until the current row is saved and the [Row ID] shows up. Otherwise you see an error until the row is saved. The error will go away - it just avoids confusion if anyone is doing direct entry upon the sheet. If your tasks come in via a form this is a moot point. But it doesn't hurt anything to have the IF.
Does this get you what you need?
Kelly
Hey @Chris Jones99
Here's one approach.
Add the system autonumber column to your Main Data sheet. Save. This will create the column [Row ID].
On your People sheet, have 2 columns. Names (which your Advisors will update daily), and Order column. The order column contains the numbers 1, 2 and 3. These numbers are NOT updated. Consider locking this column.
Insert this formula into your 'assigned person' column
=IF(MOD([Row ID]@row/3)=0, INDEX({People sheet Names column}, MATCH(3,{People sheet Order column},0)), IF(MOD([Row ID]@row/2)=0, INDEX({People sheet Names column}, MATCH(2,{People sheet Order column},0)), INDEX({People sheet Names column}, MATCH(1,{People sheet Order column},0))))
In words, the formula sees if the [Row ID] is a whole number when divided by the number 3. If it is, the index/match does a lookup where it looks for the name in the People sheet associated with the number 3. If the [Row ID] is not evenly divisible by 3, the formula checks if [Row ID] is evenly divisible by 2. If yes, it finds the name associated with #2. If neither 3 or 2 division produce a whole number, the formula will default to the name associated with 1. This pattern will repeat forever.
Remember because this is using cross sheet references you cannot simply copy paste but must physically create the ranges using the Reference another sheet link in the formula dialog box.
Will this work for you?
Kelly
Good Morning, thank you for your help. My formula expertise is not the best and unfortunately. trying a couple of times I can't get the above to work.
#incorrect argument set
the below is how the formula looks in the sheet.
=IF(MOD([Row ID]@row / 3) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(3, {Re-Delivery Team - Allocator Range 2}, 0)), IF(MOD([Row ID]@row / 2) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(2, {Re-Delivery Team - Allocator Range 2}, 0)), INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(1, {Re-Delivery Team - Allocator Range 2}, 0))))
Hey Chris
I meant to add commas instead of slashes in the MOD function. I guess my brain was still thinking division. Here's the correction.
=IF(MOD([Row ID]@row,3)=0, INDEX({People sheet Names column}, MATCH(3,{People sheet Order column},0)), IF(MOD([Row ID]@row,2)=0, INDEX({People sheet Names column}, MATCH(2,{People sheet Order column},0)), INDEX({People sheet Names column}, MATCH(1,{People sheet Order column},0))))
I think this will fix it.
Kelly
I am most grateful for your help that worked perfectly! I am really pleased with the results.
I have one last thing to ask. The current list of 3 can go up or down depending on who is working. i.e we could have 5 people in one day and the next day just 2. When I add the additional formula to look at this new bigger list, I get a very uneven allocation. For example, person on 'Order 3' gets allocated a lot more than the other 4 in the list.
Is there a way to help even this out. So it doesn't really matter if there are 2,3 or 5 people, it will nearly always be a round robin type effect and provide an even split of work between them?
Thank you again.
New formula
=IF(MOD([Row ID]@row, 3) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(3, {Re-Delivery Team - Allocator Range 2}, 0)), IF(MOD([Row ID]@row, 4) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(4, {Re-Delivery Team - Allocator Range 2}, 0)), IF(MOD([Row ID]@row, 5) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(5, {Re-Delivery Team - Allocator Range 2}, 0)), IF(MOD([Row ID]@row, 2) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(2, {Re-Delivery Team - Allocator Range 2}, 0)), INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(1, {Re-Delivery Team - Allocator Range 2}, 0))))))
Hey Chris
The formula with Nested IFs must be in descending order of count. This will correct the formula for 5 people.
=IF(MOD([Row ID]@row, 5) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(5, {Re-Delivery Team - Allocator Range 2}, 0)), IF(MOD([Row ID]@row, 4) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(4, {Re-Delivery Team - Allocator Range 2}, 0)), IF(MOD([Row ID]@row, 3) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(3, {Re-Delivery Team - Allocator Range 2}, 0)), IF(MOD([Row ID]@row, 2) = 0, INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(2, {Re-Delivery Team - Allocator Range 2}, 0)), INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(1, {Re-Delivery Team - Allocator Range 2}, 0))))))
I understand though that your team varies- I worried about that when I gave you the first nested IF. I've been working on a solution to make this dynamic. I haven't got it quite right yet.
Kelly
Hey Chris
There is a solution to your request, thanks to a community post by Bob Kahn
Row #1 of your intake sheet will always skip person #1 in the sequence, so the initial sequence on the Main sheet runs 2-whatever. All subsequent sequences follow the desired pattern 1-whatever. Each day when you update the People sheet, you will now also need to update the Order column to match the number of people in the list. Always start with #1 in your sequence list. You must have a #1. It should only be the one time on the sheet that the sequence skips a person.
=IF([Row ID]@row <> "", INDEX({Re-Delivery Team - Allocator Range 1}, MATCH(MOD([Row ID]@row, MAX({Re-Delivery Team - Allocator Range 2})) + 1, {Re-Delivery Team - Allocator Range 2}, 0)))
The IF in the formula just keeps the Name blank until the current row is saved and the [Row ID] shows up. Otherwise you see an error until the row is saved. The error will go away - it just avoids confusion if anyone is doing direct entry upon the sheet. If your tasks come in via a form this is a moot point. But it doesn't hurt anything to have the IF.
Does this get you what you need?
Kelly
Big thank you for finding this and all your help this past week.
This works perfectly and achieves exactly what we need.
Thank you again.
Hi Kelly,
I have followed this post and I was wondering how you would handle the "People & Sequence number" if they were added to that sheet via sign-in form. The people sheet would have the date & time, the contact, and a sequence number. In this way the assignment process to the main sheet would be managed via a form by the users that sign in for that day.
Cheers,
Peter Leddington
