Formula to copy across columns for football comp Cell reference error

[Alpacino]

Hi all,

Wonder if you can help.

Trying to find a formula that be copied across columns to split a string containing numbers into two adjest columns. however to my frustration when I try to copy the formula it doesn't pull the correct cell reference.

Can anyone help with the formula. Basically I am using Value(Mid(B2,2,1)) for home and away Value(Right(B2,1))

Thank you in advance

[TABLE="width: 643"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD] [/TD]
[TD]Claire[/TD]
[TD] [/TD]
[TD]Dave[/TD]
[TD] [/TD]
[TD]Bill[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Fixture[/TD]
[TD]Bob[/TD]
[TD]Claire[/TD]
[TD]Dave[/TD]
[TD]Bill[/TD]
[TD][/TD]
[TD]H[/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]QPR v Stoke[/TD]
[TD]H1A2[/TD]
[TD]H1a1[/TD]
[TD]H1a2[/TD]
[TD]H1A1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Aston villa v Arsenal[/TD]
[TD]H0A2[/TD]
[TD]H1a0[/TD]
[TD]H1a3[/TD]
[TD]H1A2[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Newcastle v Hull[/TD]
[TD]H1A2[/TD]
[TD]H1a3[/TD]
[TD]H1a3[/TD]
[TD]H1A1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Burnley V Sunderland[/TD]
[TD]H1A1[/TD]
[TD]H0a0[/TD]
[TD]H1a0[/TD]
[TD]H2A2[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Swansea v Southampton[/TD]
[TD]H2A2[/TD]
[TD]H0a2[/TD]
[TD]H2a2[/TD]
[TD]H2A1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tottenham v West Brom[/TD]
[TD]H2A2[/TD]
[TD]H2a1[/TD]
[TD]H3a1[/TD]
[TD]H3A1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]West Ham v Liverpool[/TD]
[TD]H1A3[/TD]
[TD]H1a3[/TD]
[TD]H2a3[/TD]
[TD]H1A2[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Leicester v Man United[/TD]
[TD]H1A1[/TD]
[TD]H0a2[/TD]
[TD]H1a4[/TD]
[TD]H1A3[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Everton v Crystal Palace[/TD]
[TD]H2A0[/TD]
[TD]H3a1[/TD]
[TD]H3a1[/TD]
[TD]H3A1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Man City v Chelsea[/TD]
[TD]H2A2[/TD]
[TD]H1a2[/TD]
[TD]H1a1[/TD]
[TD]H1A1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col span="4"><col><col span="8"></colgroup>[/TABLE]

 

[BobUmlas]

There's no way to get the pattern b,b,c,c,d,d,e,e by filling right. Easiest would be to manually enter these formulas into the first row, then fill down. It COULD be done, but the formula would be somewhat involved:
For the first:
=1*MID(INDEX(2:2,INT(COLUMN()/2)-1),2,1)
for the 2nd:
=1*RIGHT(INDEX(2:2,INT(COLUMN()/2)-1),1)
select both these formulas and fill right.