# Formula to copy across columns for football comp Cell reference error

#### Alpacino

##### Well-known Member
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

 Bob Claire Dave Bill Fixture Bob Claire Dave Bill H A H A H A H A QPR v Stoke H1A2 H1a1 H1a2 H1A1 1 2 Aston villa v Arsenal H0A2 H1a0 H1a3 H1A2 0 2 Newcastle v Hull H1A2 H1a3 H1a3 H1A1 1 2 Burnley V Sunderland H1A1 H0a0 H1a0 H2A2 1 1 Swansea v Southampton H2A2 H0a2 H2a2 H2A1 2 2 Tottenham v West Brom H2A2 H2a1 H3a1 H3A1 2 2 West Ham v Liverpool H1A3 H1a3 H2a3 H1A2 1 3 Leicester v Man United H1A1 H0a2 H1a4 H1A3 1 1 Everton v Crystal Palace H2A0 H3a1 H3a1 H3A1 2 0 Man City v Chelsea H2A2 H1a2 H1a1 H1A1 2 2

<tbody>
</tbody><colgroup><col><col span="4"><col><col span="8"></colgroup>

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.

Replies
2
Views
717
Replies
5
Views
999
Replies
3
Views
3K

1,221,059
Messages
6,157,681
Members
451,433
Latest member
AndyB86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back