Randomise Analysts to Allocate Work

[Steven1985]

Hi, Does anyone know how I can create a list with the Analysts names to be randomised each day to allocate accounts to work?

There will be 6 Analysts. In column A1 will be the the work required to action, in column W1 I have Analyst allocation.

I want a formula or macro to create somthing that will, on each day, open the spreadsheet and will automatically imput the analysts name to an account.

Thanks

 

[SteveO59L]

Use Rand() and Rank() with Vlookup()

 

[Steven1985]

Do i need to create a seperate tab with analysts names? Im new to excel so if you wouldnt mid explaining abit more please?

 

[SteveO59L]

How many accounts would there be ?

 

[Steven1985]

It will vary each day but id guess MAX 300.

 

[SteveO59L]

Ok,

I have account numbers in A1:A300, and randbetween(1,6) in b1:b300.

The table of analyst id ( 1 - 6) and analyst names in A313:b318.

In W1 and filled down to w300

=VLOOKUP(B1,$A$313:$B$318,2,FALSE)

However, every time the sheet recalculates, the allocations would change.

 

[Steven1985]

I cant add additional columns onto the spreadsheet.

Account numbers on in Column I.

I have opened a new tab to create a table of Analysts. A1:B6. In cell C1 i have the formula =randlookup(1, 6) and i have filled that down to C6.

I have then tried to work the formula in Cell W1 'Allocation' but wont work

=VLOOKUP(Sheet1!C1, Sheet1!A1:B6, 2, FALSE)

Is this because im taking the data from another tab?

 

[SteveO59L]

No, if you want to allocate the analyst to the work, you need to relate the analysts id to the work.

 

[Steven1985]

Still not able to get it to work. I can randomise 1 - 6 and fill down and that works but I cant get it to associate with the analyst names.

 

[SteveO59L]

send me a pm with your e-mail address. and I'll send you a sample file