mcsdguyian
asked on
Excel Row Increment
I have an Excel workbook with 2 sheets "A" and "B". I have multiple pieces of data to pull out and I need to figure out how to increment the Rows and grab the data from SheetA to SheetB and then drag that formula down X amount of rows.
For example I want to pull data from the initial CELL G2 and then every 92 rows after that so G2, G94, G186, Etc... and this happens for hundreds of rows so I don't want to do this manually.
I would appreciate any help.
For example I want to pull data from the initial CELL G2 and then every 92 rows after that so G2, G94, G186, Etc... and this happens for hundreds of rows so I don't want to do this manually.
I would appreciate any help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try making it this version to start
=OFFSET(SheetA!G$2,(ROWS(B $3:B3)-1)* 92+COLUMNS ($B3:B3)-1 ,0)
The columns part will increment to G3, G4 etc as you copy across
regards, barry
=OFFSET(SheetA!G$2,(ROWS(B
The columns part will increment to G3, G4 etc as you copy across
regards, barry
ASKER
Hi Barry,
That code is not working I may not have explained very well.
I am starting this particular formula in Cell(B6) and the formulas in the following cell are below
Cell(B6) = "=OFFSET(A!I$5,(ROWS(B$6:B 6)-1)*92,0 )"
Cell(C6)= "=OFFSET(A!R$5,(ROWS(C$6:C 6)-1)*92,0 )"
Cell(D6)= "=OFFSET(A!AB$5,(ROWS(D$6: D6)-1)*92, 0)"
I need to skip Cell(E6) I was just copying and pasting B6, C6, D6 into F6, G6, H6 and they need to look like the following.
Cell(F6) = "=OFFSET(A!I$6,(ROWS(F$6:F 6)-1)*92,0 )"
Cell(G6)= "=OFFSET(A!R$6,(ROWS(G$6:G 6)-1)*92,0 )"
Cell(H6)= "=OFFSET(A!AB$6,(ROWS(H$6: H6)-1)*92, 0)"
Skip Cell(I6) [Example continued]
Cell(J6) = "=OFFSET(A!I$7,(ROWS(J$6:J 6)-1)*92,0 )"
Cell(K6)= "=OFFSET(A!R$7,(ROWS(K$6:K 6)-1)*92,0 )"
Cell(L6)= "=OFFSET(A!AB$7,(ROWS(L$6: L6)-1)*92, 0)"
I hope I implemented your first part of the code correctly it works perfect for the rows copying down.
When I copy B6, C6, D6 and paste them into F6, G6, H6 the wrong part of the formula increments. I get
Cell(F6) = "=OFFSET(A!M$5,(ROWS(F$6:F 6)-1)*92,0 )"
INSTEAD OF "=OFFSET(A!I$6,(ROWS(F$6:F 6)-1)*92,0 )"
Cell(G6)= "=OFFSET(A!V$5,(ROWS(G$6:G 6)-1)*92,0 )"
INSTEAD OF "=OFFSET(A!R$6,(ROWS(G$6:G 6)-1)*92,0 )"
Cell(H6)= "=OFFSET(A!AF$5,(ROWS(H$6: H6)-1)*92, 0)"
INSTEAD OF "=OFFSET(A!AB$6,(ROWS(H$6: H6)-1)*92, 0)"
Thanks for your time and Expertise Barry.
Ian
That code is not working I may not have explained very well.
I am starting this particular formula in Cell(B6) and the formulas in the following cell are below
Cell(B6) = "=OFFSET(A!I$5,(ROWS(B$6:B
Cell(C6)= "=OFFSET(A!R$5,(ROWS(C$6:C
Cell(D6)= "=OFFSET(A!AB$5,(ROWS(D$6:
I need to skip Cell(E6) I was just copying and pasting B6, C6, D6 into F6, G6, H6 and they need to look like the following.
Cell(F6) = "=OFFSET(A!I$6,(ROWS(F$6:F
Cell(G6)= "=OFFSET(A!R$6,(ROWS(G$6:G
Cell(H6)= "=OFFSET(A!AB$6,(ROWS(H$6:
Skip Cell(I6) [Example continued]
Cell(J6) = "=OFFSET(A!I$7,(ROWS(J$6:J
Cell(K6)= "=OFFSET(A!R$7,(ROWS(K$6:K
Cell(L6)= "=OFFSET(A!AB$7,(ROWS(L$6:
I hope I implemented your first part of the code correctly it works perfect for the rows copying down.
When I copy B6, C6, D6 and paste them into F6, G6, H6 the wrong part of the formula increments. I get
Cell(F6) = "=OFFSET(A!M$5,(ROWS(F$6:F
INSTEAD OF "=OFFSET(A!I$6,(ROWS(F$6:F
Cell(G6)= "=OFFSET(A!V$5,(ROWS(G$6:G
INSTEAD OF "=OFFSET(A!R$6,(ROWS(G$6:G
Cell(H6)= "=OFFSET(A!AF$5,(ROWS(H$6:
INSTEAD OF "=OFFSET(A!AB$6,(ROWS(H$6:
Thanks for your time and Expertise Barry.
Ian
ASKER
Is what I am asking even possible? If not just let me know Barry and I will close this and award you the points.
Thanks
Thanks
ASKER
When I copy it and paste it to the other Columns though it does not increment the SheetA!G$2, the Number "2". Is that possible without breaking the functionality of being able to use it for the rows?
Thanks,
Ian