• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Draw for Secrect Friends (Hidden Friends) without vba

chuckbilly

New Member
I'd like to suggest develop a system (with no vba code, just formulas) to draw for the secret friend for Christmas.
 
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,B1)=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.
 
Back
Top