Return next name in a list-a rotation

Hi wanabacat (hate dogs?)

See if the formula in the attached sheet works for you.

A changing list of salespeople...

If change "one leaves, another replaces them immediately" then the number of sales people doesn't change and you can assign Salesperson1, Salesperson2, ... and just change the names of those salespeople. (Its equivalent to an unchanging sales force.)

If the situation is that sometimes they leave, sometimes there are hires, but not simultaniously, what ar your procedures?

If you a salesperson leaves, are their accounts assigned to the remaining sales force?

Of more import, what do you do when you hire a new sales person, do existing accounts get assigned to that new person, without regard for the existing client/salesperson relationship?

As a record keeper, Excel would be good for client>salesperson.

But using a logical procedure to assign clients to salespeople will result in human issues.

Hi,

To answer this problem correctly I/we need some more details.
Does the order of the Salesperson list ever change?
Will there be more than 1 salespeople deleted off the list at a time?
Does the salesperson name need to stay with that customer after they are deleted from the list, or updated with another salesperson's name?
What should happen if/when a person sorts the salesperson list?

Can I/we use VBA code to help with this problem? I tried not to use code in my last answer, that I didn't like much, BTW. I'll wait for your answer before working on some code.

I'm having trouble dealing with this problem in VBA...

What if the person just above the one about be entered has been deleted from the list on sheet 2?

So my program is going to look for the salesperson just above the one about to be entered in the list on sheet2. What if they are gone? Should I look two places above the person about to be entered? Then, what if this is the first person in the list. I need to look at the bottom person? confused:

I seem to be on an "off" day on how to resolve these exceptions. I'll work on it later.

OK wannabacat (with kittens).

To make this work I needed to have more stuff on the Salesperson sheet. I've added 2 columns and an extra table. Instead of removing a salesperson name you will need to simply make them Inactive using Column B validation dropdown. If you add a salesperson you will also need to make them active. Then in Column C pull down the formula as I need this to build the Active salesperson table in Column E. Column E formulas need to be pulled down far enough to include all Active salespeople.

NOW - the macro gets triggered when you enter (or change) something in Col A AND Col C in that same row is blank. The macro will find the next active salesperson in line and put them below the last name in Col C.

I hope this works for you. See the attached. Private Message me if it doesn't work as I'll see a PM before an updated post.

Here's a solution that uses only formulas (no VBA) but you need to give the salespersons start and finish dates so only salespeople working at teh particular date of a new job get allocated. It should if it works properly allocate the next available salerep that is working on the appropriate date. If a salesperson is working then I simply put their finish date as a long time in the future (2099). If new sales people start simply add them to the bottom of the list. If a sales person finishes then put his finish date in so he doesn't get allocated in future events.

good luck - please test though
rotating%20list(2).xlsx

Edit Sorry I just changed the file to the correct one

Hi Crooza,

Great formula, it is something I need to study to understand.

The problem is that the salespeople will change when with new or old hire dates. For example, what happens if a salesperson takes a leave and comes back later to work again?

I believe the OP needs to have the Salesperson put into Column C using VBA so no formula is needed. That way they won't change with other changes. I believe this then mandates a VBA solution and not formulas.

MarvinP Yeah its a bit confusing. You might be right VBA might be the answer but I still think the formula basis works - you just need to put sales reps on annual leave as finishing and when tyhey return create a new line for them. I've allowed 49 lines of data for sales reps but I don't see why you couldn't make it longer to accomodate a bigger team and the fact that staff will be going away and returning. Of course there are some issues for both VBA AND formula in regards to what happens to a salesreps clients when he goes on leave or resigns. Do these customers need to be reallocated to another salesrep?

Anyway to help you understand the formula it's simply trying to get an array of all the index positions (row number -1) from the sales team that are available in the date period for the customer AND that are greater than the previous matched salesrep. The arrays look something like this {0,2,3,0,5} meaning that salesreps 2,3 and 5 are available. If the last sales rep was 3 then it elimates 1,2 and 3 and then converts 0's to 10's and takes the minimum left. It will look something like this {10,10,10,10,5} so the next available salesrep is the lowest being 5. If it returns an error it simply goes back to the minimum from the original array (substituting 0's for 10's) so it looks like this {10,2,3,10,5} so the minimum is 2 in this case and goes back to the top of the avaiable list of salesreps. If there's an easier way to do this let me know. As I said earlier it allows salesreps to be added and chnage their date so their availability is always checked and included as part of the allocation.

Great to hear this solves this, easy to state and hard to solve, problem.

Sorry I needed to add a few extra columns. The column E could be used as a Validation list of Active salespeople. If that isn't a need then I could use VBA to determine the next person in the line:

Please Login or Register  to view this content.

Which is really the first active person in the A column List.

Here is code that would allow you to eliminate Columns E and C if you wanted to clean up your Salespeople sheet.

Please Login or Register  to view this content.

Thanks for the rep.