Christmas presents balloting codes

[yocin]

Hi experts ! As Christmas is coming, my ex-classmates are coming to gather for a gift exchange next week. But as all of us stay at different towns, we are unable to a meet up prior to the gift exchange for our balloting - who shld buys whose gift. Hence I'm here wondering whether is there any codes that can be implemented to make our balloting a simple one via online.

The requirement of the balloting is that:

1) One cannot pick their own name (Couldn't possible buy their own gift for themselves. hehe)
2) The gift exchange name can only be picked once.

I'm thinking of logically on it shld/can works.

There should be already prefixed names as we know who are attending. E.g 6 persons - Andy, Chris, Cindy, Nelly, John, Alice.

Upon opening the webpage, the person is to choose their name from the prefixed names. This is to guarantee the person cannot pick their own names for the gift exchange. Codes might read like gift exchange name cannot be equal to the selected prefixed name.

2nd, the gift exchange name can only be picked once. E.g Alex cannot be picked by both Cindy & John. In another words, no person should be left out in the gift exchange.

Pls recommend me on I can go about doing the codes, of cause the balloting should be randomly assigned by the system. I cannot hardcode e.g Alex to pick Alice. Alice to pick Nelly, Nelly to pick Alex etc.

Will it turned out too complex? haha, pls advise me - an I.T idiot. Thanks all Advance Merry Christmas to all

[Bob Phillips]

Put a list of names in say M1:M10

Then, ensure cell A1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B1>0,$B1<>$C1,COUNTIF(B$1:B$10,B1)=1)),B1,RANDBETWEEN(1,10))
it should show a 0

Then, type this formula into cell C1
=IF(($A$1="")+(AND(C1>0,$B1<>$C1,COUNTIF(C$1:C$10,C1)=1)),C1,RANDBETWEEN(1,10))

In D1, enter
=INDEX($M$1:$M$10,B1)
and copy across to E1

Copy B1:E1 down to B10:E10.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change, and you will see unique name pairs.

To force a re-calculation, clear cell A1, edit cell B1 and C1, don't change them,
just edit to reset to 0, copy B1:C1 down to B10:C10, and re-input A1.