Allocate random selections from Excel list

[newmanje]

I need to produce a spreadsheet that will allow me to team up members of staff and tally their weekly sales points. It needs to show me who is paired with who each week.

The difficulty is that the spreadsheet needs to randomly pair up 2 members from the staff list each week then at the end of the week when their points are logged against their names it shows the sum of their points against the team.

I've spent a couple of hours experimenting trying to ramdomise lookup lists but I can't even get close to working out how to omit the user's own name as a potential partner or then making sure that a 2nd partner doesn't get allocated to more than one person.

I think I'm going out of my tiny mind!

 

[SkipVought]

Hi

check out Data/Validation - Custom with a formula like this in the L2 validate...
[tt]
=ISERROR(MATCH(L2,$L$1:L1,0))
[/tt]


Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[newmanje]

Thanks Skip but I'm still struggling. I'll try it again in the morning and see if I get anywhere.

Jim

 

[SkipVought]

In my example, the validation RANGE is L2:Lthenumberofpossiblepairs+1

The FIRST value goes into L1

If a DUPLICATE is entered in L2 and following, the Validation rule fires.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}