Reversing Sports Game Fixtures (e.g. making team A, team B etc.) in a List

**mrvp** · 12-06-2012, 05:29 PM

Hi,

Another slightly sports related query - in short I am rating fixtures again and the latest method I have needs me to take both sides of the game (e.g. so I have the margin of victory for the home team, and the margin of victory for the away team) but because of other array formulas that I need to use (which aren't in the attached sheet) I need the home and away team in the same column (in the case of the attached column B).

Basically in the attached (first 11 weeks of this years NFL season), I have just referenced the original (e.g. H3 is =D3) in columns H, I, J and K to reverse the fixture around (making Team A, Team B and making Team B, Team A) and then pasted in them in row 163 down (note the week flip over in column A, which I just manually pasted from the above rows).

In short is there a neater way of doing this or am I trying to oversimplify something (e.g. when I add a subsequent weeks fixtures, is just a case of insert some rows at 163, paste the new original fixtures in, and add the flipped fixtures on the bottom)?

Thanks in advance for any ideas or suggestions.
Fixture Rework.xls

**JieJenn** · 12-06-2012, 05:44 PM

Cross that. The formula is wrong.

You probably won't like this.

If you want to use a formula, you can probably try enter this in H3 and drag it across and down

=INDIRECT(ADDRESS(ROW(),COLUMN()-4 - (2 * (COLUMN()-8))))

**mrvp** · 12-06-2012, 06:02 PM

Originally Posted by JieJenn

Cross that. The formula is wrong.

You probably won't like this.

If you want to use a formula, you can probably try enter this in H3 and drag it across and down

=INDIRECT(ADDRESS(ROW(),COLUMN()-4 - (2 * (COLUMN()-8))))

Like the idea and it is more elegant in principle (didn't know there was an indirect and address formula - but do now and it is a cleaner way in comparison to my basic idea), but that would put the week in the Team B points column (K) - columns H, I and J are fine. Because column E is also Team B points, and I have H as the reversed Team A, this would need a tweak.