sports draw

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?
>