Formula Help Please

Copper Contributor

excelcupknockoutphoto.jpg

 

Don't think this worked last time, so I'll try again.

 

I want to auto-fill team names in rounds 2 and so on.

In Round 1, you can see that, in Tie1, NLBOT11 won 5-4 away to NLBOT1. 

 

So, I am looking for a formula (IF/Or, OR A COMBINATION OF BOTH) that auto-fills the winners of Tie1 into CELL J1 and then the winners of TIE2 are auto-filled in to J2, and then the formulas continue for the rest of Round 1 winners to be automatically entered into J Column cells accordingly.

 

Help please!

2 Replies

@Carl1983 

Hi, you aren't going to like this too much, because the way you have set it up has no helper columns (which would make your life a lot easier).  

 

For column J, I would use the following in cell J1:

 

=OFFSET($C$1,(ROWS($I$1:I1)-1)*2,(OFFSET($D$1,(ROWS($I$1:I1)-1)*2,)<OFFSET($F$1,(ROWS($I$1:I1)-1)*2,))*COLUMNS($D1:$G1))

 

For column N, I would use the following in cell N1:

 

=OFFSET($C$1,(ROWS($I$1:I2)-1)*2,(OFFSET($D$1,(ROWS($I$1:I2)-1)*2,)<OFFSET($F$1,(ROWS($I$1:I2)-1)*2,))*COLUMNS($D2:$G2))

 

These formulae could be a heck of a lot simpler if you had helper columns to number matches in a particular way (you could tie them to the TIE numbering).

 

Some might not like my use of OFFSET and COLUMNS.  OFFSET is a volatile function (it recalculates all of the time), but it is useful when your ranges may vary.  Alternatively, you could use INDEX.

 

COLUMNS is used similarly in case you add any more columns in between the two teams playing in each round.

 

Try considering structure more going forward.  With a better layout, your formulae would be trivial.

 

Check out www.sumproduct.com/thought for more free tips and downloads.

Forgot to add my example @Liam Bastick