I want to set up a Sports Draw for 6 teams. If I list the teams and add the
weekly dates, can I have Excel use a formula to insert the team names?

Cells B1 to F1: 1
Cells B2 to B7: the values 2 to 6
Cell C3: =B7
Cell C4: =B3. Copy down to C7.
Copy C2:C7 to C2:F7
Cells A9 to A14: the numbers 1 to 6
Cell B9: =7-MATCH($A9,B$2:B$7,0). Copy to B9:F14
Cell B16: =INDEX(B$2:B$7,B9). Copy to B16:F21
B16:F21 gives the list of opponents for the weeks.


"Warwick" wrote:

I want to set up a Sports Draw for 6 teams. If I list the teams and add the
weekly dates, can I have Excel use a formula to insert the team names?

To get this to work I had to make the changes I've indicated below:


Cells B1 to F1: 1
Cells B2 to F2

Cells B2 to B7: the values 2 to 6
Cells B3 to B7: the values 2 to 6

Cell C3: =B7
Cell C4: =B3. Copy down to C7.
Copy C2:C7 to C2:F7
Cells A9 to A14: the numbers 1 to 6
Cell B9: =7-MATCH($A9,B$2:B$7,0). Copy to B9:F14
Cell B16: =INDEX(B$2:B$7,B9). Copy to B16:F21
B16:F21 gives the list of opponents for the weeks.


"Warwick" wrote:

I want to set up a Sports Draw for 6 teams. If I list the teams and add the
weekly dates, can I have Excel use a formula to insert the team names?

You are quite right. I had row 1 as a blank row. Thank you for checking,
pointing out the error and correcting.

"Karsen" wrote:


To get this to work I had to make the changes I've indicated below:


Cells B1 to F1: 1
Cells B2 to F2

Cells B2 to B7: the values 2 to 6
Cells B3 to B7: the values 2 to 6

Cell C3: =B7
Cell C4: =B3. Copy down to C7.
Copy C2:C7 to C2:F7
Cells A9 to A14: the numbers 1 to 6
Cell B9: =7-MATCH($A9,B$2:B$7,0). Copy to B9:F14
Cell B16: =INDEX(B$2:B$7,B9). Copy to B16:F21
B16:F21 gives the list of opponents for the weeks.


"Warwick" wrote:

I want to set up a Sports Draw for 6 teams. If I list the teams and add the
weekly dates, can I have Excel use a formula to insert the team names?