A
andrew
I'm assisting a school mini-league for hockey, and would have the following
table score set up.
Wk Date Home Score Away
1 13-Jul A 2 1 B
1 13-Jul C 3 0 D
1 13-Jul E 2 2 F
2 20-Jul F 0 1 A
2 20-Jul B 1 0 C
2 20-Jul D 1 1 E
3 27-Jul A 0 0 D
3 27-Jul C 2 3 F
3 27-Jul E 1 1 B
The above is a summary sheet (titled 'Fixture') of the fixtures (the scores
are just dummy data). I have 6 individual team sheets, and would like each
team sheet to grab the following:
a) list of weekly opponents for each team. Using above example, worksheet
for Team A (titled 'A') shows them playing at home for wk 1, then away wk 2,
then home again wk 1. I tried using VLOOKUP to check and display the
opponents in each row using this formula:
=IF(SUMPRODUCT(--(Fixture!A1:A9=A3),--(Fixture!A1:F9="A")),VLOOKUP(A3,Fixture!A1:F9,6,FALSE),VLOOKUP(A3,Fixture!A1:F9,3,FALSE))
where in sheet titled 'A', A3=1 (week 1) but i keep getting #VALUE!.
Is there any way to list from week 1 to week 3 within the team sheets to
show the list of opponents for easy reference? Appreciate any help, thanks!
table score set up.
Wk Date Home Score Away
1 13-Jul A 2 1 B
1 13-Jul C 3 0 D
1 13-Jul E 2 2 F
2 20-Jul F 0 1 A
2 20-Jul B 1 0 C
2 20-Jul D 1 1 E
3 27-Jul A 0 0 D
3 27-Jul C 2 3 F
3 27-Jul E 1 1 B
The above is a summary sheet (titled 'Fixture') of the fixtures (the scores
are just dummy data). I have 6 individual team sheets, and would like each
team sheet to grab the following:
a) list of weekly opponents for each team. Using above example, worksheet
for Team A (titled 'A') shows them playing at home for wk 1, then away wk 2,
then home again wk 1. I tried using VLOOKUP to check and display the
opponents in each row using this formula:
=IF(SUMPRODUCT(--(Fixture!A1:A9=A3),--(Fixture!A1:F9="A")),VLOOKUP(A3,Fixture!A1:F9,6,FALSE),VLOOKUP(A3,Fixture!A1:F9,3,FALSE))
where in sheet titled 'A', A3=1 (week 1) but i keep getting #VALUE!.
Is there any way to list from week 1 to week 3 within the team sheets to
show the list of opponents for easy reference? Appreciate any help, thanks!