• Status: Solved
• Priority: Medium
• Security: Public
• Views: 425

# 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.
0
mcsdguyian
• 3
• 2
1 Solution

Commented:
Assuming you put the first formula in SheetB cell B3 try this

=OFFSET(SheetA!G\$2,(ROWS(B\$3:B3)-1)*92,0)

copy the formula down

If your start cell is somewhere other than B3 then change the ROWS(B\$3:B3) part accordingly

regards, barry
0

Author Commented:
Thanks Barry that is what I a looking for it works perfect for the rows I need to use this for about 150 columns also and I figured I could just copy and paste and the ROWS(B\$3:B3) part increments to ROWS(C\$3:C3), ROWS(D\$3:D3), etc.. No Problem there

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
0

Commented:
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
0

Author Commented:
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:B6)-1)*92,0)"
Cell(C6)= "=OFFSET(A!R\$5,(ROWS(C\$6:C6)-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:F6)-1)*92,0)"
Cell(G6)= "=OFFSET(A!R\$6,(ROWS(G\$6:G6)-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:J6)-1)*92,0)"
Cell(K6)= "=OFFSET(A!R\$7,(ROWS(K\$6:K6)-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:F6)-1)*92,0)"

Cell(G6)= "=OFFSET(A!V\$5,(ROWS(G\$6:G6)-1)*92,0)"

Cell(H6)= "=OFFSET(A!AF\$5,(ROWS(H\$6:H6)-1)*92,0)"

Thanks for your time and Expertise Barry.

Ian
0

Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.