Display a name from a cell range, round-robin
Another day, another Google Sheets (Excel-like) challenge. :)
This time, I have a column of team names. The team names may or may not have an entry code associated with their team.
I spent nearly 2 hours searching the internet for answers. It seems that no one has tried to do this with just formulas. I found a few examples, but they required scripting. :(
The challenge is that I wish to automatically assign a reviewer (from Reviewer List column) if the team has an entry code. But in doing so, I want to assign a reviewer round-robin style, so that each reviewer is assigned approximately an equal number of teams.
Please see example sheet:
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=964764875
The way I am currently doing this isn't very smart, names are not used sequentially (and round-robin), every time there is a value in the Shared Entry column. What I wish to see is what is shown in the Expected Results column.
But to accomplish this challenge, it will need to work in Google Sheets, use only formulas, and work without add-ons.
Is this even possible?
Thank you so much
:)
This time, I have a column of team names. The team names may or may not have an entry code associated with their team.
I spent nearly 2 hours searching the internet for answers. It seems that no one has tried to do this with just formulas. I found a few examples, but they required scripting. :(
The challenge is that I wish to automatically assign a reviewer (from Reviewer List column) if the team has an entry code. But in doing so, I want to assign a reviewer round-robin style, so that each reviewer is assigned approximately an equal number of teams.
Please see example sheet:
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=964764875
The way I am currently doing this isn't very smart, names are not used sequentially (and round-robin), every time there is a value in the Shared Entry column. What I wish to see is what is shown in the Expected Results column.
But to accomplish this challenge, it will need to work in Google Sheets, use only formulas, and work without add-ons.
Is this even possible?
Thank you so much
:)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exceptional answer. Thank you, Byundt! :)
ASKER
Pure brilliance!
ASKER
Hello byundt,
Thank you again for that elegant formula. :)
I am trying to decypher the formula to try to understand it a bit better. Can you explain it a bit?
I would like to make a minor modification so that instead of being triggered by any value in the Shared Entry column, only values beginning with ASC will trigger a name.
I originally thought that I could just change,
=if($C4="","",
to
=if($C4="ASC*","", but clearly I am not understanding how this works. :(
Thank you. :)
Thank you again for that elegant formula. :)
I am trying to decypher the formula to try to understand it a bit better. Can you explain it a bit?
I would like to make a minor modification so that instead of being triggered by any value in the Shared Entry column, only values beginning with ASC will trigger a name.
I originally thought that I could just change,
=if($C4="","",
to
=if($C4="ASC*","", but clearly I am not understanding how this works. :(
Thank you. :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, again. Another brilliant answer! :)
Open in new window