excel problems

[excalibur3]

I'm trying to set up a game of assassins which requires everyone on a list to be given a person to go after with a sock. There is a one to one pairing with no repeats but if i have someone they don't necessarily have me. I'm trying to make an excel spread sheet to give me a randomly generated pairings but I'm having troubles. I found a random number generator so I can generate a random number for each person that will never repeat one above it but I can't figure out how I can basically add a =A before it in the cell so that instead of a number it will reference a cell (The names are in A1-Ax). I've tried to use macros but with minimal luck. What is the best way of doing this? I have like 300 people to pair up and it is critical that it is random and non repeating.

 

[Thyme]

I'm not sure if I understand your problem, but is =cell() what you're looking for?

 

[mayest]

If I understand, you have a random number generator that essentially randomly sorts a list of numbers from 1 to 300, right? Then, you place this list in, say, column B right next to the 300 names that you have. Now, let's say that the first person's random number is 229 (meaning that they are paired up with the person in row 229). It seems that you want to pull that person's name and display it next to their partner, right?

Assuming the names are in column A, and the random numbers are in B, then I would use the following function in column C:

=INDIRECT("A"&TEXT(B1,"#"))

If your random number is 229, then that will reference cell A229. Copy that down to get all of the pairings.

BTW, I assume that your random number generator is in VBA and is placing actual numbers into the cells in column B. You want to avoid using a volatile function like Rand or Randbetween because they will change the pairings every time the worksheet is recalculated.

 

[theknight571]

I know I'm late (by a couple of days) into this conversation, but:

If all you need to do is matchup names. You could use a Secret Santa program. 🙂