Display a name from a cell range, round-robin (revisited) - but leave name permanent, once picked
Hello all,
I am looking for a little more help to solve a small flaw in an idea I had for a round-robin auto name selector.
Based on a specific condition, where "ASC" exists in column C, an assigned reviewer will automatically populate column J, from a pool of names in column L.
This works very well, except for one problem. The names will dynamically change, if new entries become available in column C. I am trying to find a way to lock down the name in column J, once the condition is met.
I was thinking that the solution could be to only add a name (from L reviewers list), if the cell in column K is empty. Once the cells in column K are populated, the name will not change. The possible caveat to this is that I also want the names to be evenly distributed.
Any ideas on how this can be accomplished? Please see sample sheet.
Thanks to the tremendous help from EE, I have some really nice formulas for automation in the following spreadsheet. Feel free to take anything from this sheet. :)
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=964764875
Thank you.
I am looking for a little more help to solve a small flaw in an idea I had for a round-robin auto name selector.
Based on a specific condition, where "ASC" exists in column C, an assigned reviewer will automatically populate column J, from a pool of names in column L.
This works very well, except for one problem. The names will dynamically change, if new entries become available in column C. I am trying to find a way to lock down the name in column J, once the condition is met.
I was thinking that the solution could be to only add a name (from L reviewers list), if the cell in column K is empty. Once the cells in column K are populated, the name will not change. The possible caveat to this is that I also want the names to be evenly distributed.
Any ideas on how this can be accomplished? Please see sample sheet.
Thanks to the tremendous help from EE, I have some really nice formulas for automation in the following spreadsheet. Feel free to take anything from this sheet. :)
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=964764875
Thank you.
ASKER
Hello Byundt,
I thank you a thousand times over, for trying. :)
I am trying to avoid scripts because they can introduce their own complications when the sheet needs to be as universal as possible.
I was thinking that this might be possible if the formula first checks the cell (in column K) is empty. If true, add an entry. When entering a name in the cell, look up column M, for the first lowest value (or 0), then use that name.
I thank you a thousand times over, for trying. :)
I am trying to avoid scripts because they can introduce their own complications when the sheet needs to be as universal as possible.
I was thinking that this might be possible if the formula first checks the cell (in column K) is empty. If true, add an entry. When entering a name in the cell, look up column M, for the first lowest value (or 0), then use that name.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help. I appreciate you for trying to help me find a solution. :)
The best way (in Excel) is to use VBA code to evaluate the logic and return the same value (if already selected) or the next reviewer up if the cell was originally blank. I believe that Google Sheets also offers some kind of programmability, but I am unable to help you write the code for it.
There is a distant possibility that you can use a circular reference type of solution (requires Iterations feature to be turned on in the File...Options...Formulas menu item). I tried unsuccessfully to devise such a solution, however.