From: Disco3Stu on 9 Mar 2010 03:07 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? From: Bob Phillips on 9 Mar 2010 03:45 How about this =MID(INDEX(\$A:\$A,ROW(A1)*20+6+COLUMN(A1)),2,2) -- HTH Bob "Disco3Stu" wrote in message news:903CD736-EDAA-4161-AB0F-533C1FD2254F(a)microsoft.com...> 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? From: Jacob Skaria on 9 Mar 2010 04:14 One way is to replace the below formula =MID(A27,23,2) with =MID(INDIRECT("A" & 27+(20*(ROW(A1)-1))),23,2) -- Jacob "Disco3Stu" wrote: > 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? From: Disco3Stu on 9 Mar 2010 05:35 Thank you, worked a treat (and saved a lot of typing!) "Jacob Skaria" wrote: > One way is to replace the below formula > > =MID(A27,23,2) > > with > > =MID(INDIRECT("A" & 27+(20*(ROW(A1)-1))),23,2) > > -- > Jacob > > > "Disco3Stu" wrote: > > > 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?  |  Pages: 1 Prev: Import multiple photos into excelNext: AutoFill Changing Wrong Value