Drag Down Formula Incorrect

[Disco3Stu]

I've got a long list of text data in column A, and am using the MID command
to extract one bit of data from each which is then inserted into four columns
(B-E) e.g.

B9 =MID(A27,23,2)
C9 =MID(A28,23,1)
D9 =MID(A29,23,5)
E9 =MID(A31,23,5)

When I drag down B9, I want the formula to change by 20 (from A27 to A47). I
have created 25 rows now, but the drag down still won't pick up the pattern
of 20 between cells.

Can anyone tell me how to achieve this?

 

[Bob Phillips]

How about this

=MID(INDEX($A:$A,ROW(A1)*20+6+COLUMN(A1)),2,2)

 

[Jacob Skaria]

One way is to replace the below formula

=MID(A27,23,2)

with

=MID(INDIRECT("A" & 27+(20*(ROW(A1)-1))),23,2)

 

[Disco3Stu]

Thank you, worked a treat (and saved a lot of typing!)