auto-populate randomly from list of names to specific location on another worksheet

I have a list of names on sheet 2. I would like to have them populate randomly into 2 person teams on sheet 1. I don't know if this is possible. I believe I'm looking for a VLOOKUP function or a combination of that and something else.

I have attached the workbook below.

Thank you for any help!

Am I on to something with this formula?

=INDEX($A$1:$A$26,RANDBETWEEN(1,COUNTA($A$1:$A$26)),1)

It should return a random value from the list of names in column A, rows 1-26, correct?

I want to copy the formula the same number of times that there are names in the list. (If there are 26 players, I want to return 26 names, but not repeat any of them to generate 13 teams of 2).

The problem is that this formula doesn't seem to work at all, let alone do all that I am trying to do.

Any ideas? Thanks!

Hi

Please click on attachment.

Cheers

Thank you very much for this.

The only problem I have is that once I enter the names, I need to enter scores. When I enter the scores, all the names change. How do I get the names to stay put once I have them all entered?

excel 1997-2003
Try setting recalculation to manual "TOOLS/OPTIONS/CALCULATION/MANUAL"

excel 2007-2010
Try setting recalculation to manual "FILE/OPTIONS/FORMULAS/WORKBOOK CALCULATION/MANUAL"

Remember to go back to change back AUTOMATIC if you going to change player vs player number.

or

you could use 'Edit - Paste Special - Value' to remove the formula.

It only way will work to stop change it every time you put in 1 cell.

If you happy? Please click solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED

If you really happy with it. Click a little star on the left bottom side.

Good luck and enjoy.

Thank you for your help!!

HI Guys

I have a similar problem to what you have but my issues is automatically populating fields within an excel spreadsheet with names. Its a rotor list of who does what jobs on a certain day, the issue is tho that one person cant do two tasks on one day and not always the same job so i wanted the sheet to randomly fill in names into the sheet from a list of names i have. Is there any way of doing this. My Ideal situation is that it takes the names sheet 2 has and fills in the columns on sheet 1 with the names, I can change the dates manually that's fine its just the names. I have attached the sheet and I have just included letters as the names as i thought this would be easier. Another complication i have is any of the inside people can do job 1,2 and 3 however only some of the list of inside people can do job 4. Sorry I know is sounds complicated but I'm sure there must be a way of doing this. The list of names that can do job 4 is highlighted in yellow if that helps.

test.xlsx

Can anyone help me please?

Thanks