Copy consecutive cells to non-consecutive cells

Need help...

I have two worksheets, sheet1 contains a column of consecutive entries in A1:A20 and sheet2 has 20 groups of data in 13x13 grids with a grid label in the upper left portion of each grid. Grid 1 fills A1:M13, grid 2 fills A14:M26, etc. The grid label is in A1,A14,A27, etc.

I am trying to populate the entries from sheet1 A1:A20 into sheet2 A1,A14...

'sheet1'!A1 to 'sheet2'!A1
'sheet1'!A2 to 'sheet2'!A14
'sheet1'!A3 to 'sheet2'!A27
'sheet1'!A4 to 'sheet2'!A40
'sheet1'!A5 to 'sheet2'!A43
.
.
.

I have used OFFSET, INDEX with no luck. I seem to get hung up with the ROW function and unfortunately don't have an example formula to post. Obviously I can go back and forth between sheets and paste the exact value but I'm trying to find a method to enter one formula in the sheet2 A1 and copy it down to the non-consecutive cells and maintain the incremental relationship from sheet1.

Any suggestions are appreciated, thanks.

Put this formula in cell A1 of sheet2:

=INDEX('sheet1'!A:A,INT((ROWS($1:1)-1)/13)+1)

then you can copy/paste it into cells A14, A27 etc., as required.

Hope this helps.

Pete

example.xlsx

The formula =INDEX('sheet1'!A:A,INT((ROWS($1:1)-1)/13)+1) is working but I wanted to see how to tweak it based on different data locations.

On sheet1 the grid labels now rest in I6:I25 and the destinations on sheet2 start at A30 then go to A43,A56,etc...

I'm not quite sure how the operators in the ROW function work.

Put this formula in A30:

=INDEX(Sheet1!I:I,INT((ROWS($1:1)-1)/13)+6)

then copy it into A43, A56, A69, and so on.

Note that the formula is now looking at column I on Sheet1, and there is a 6 at the end of the formula as your first item of data is in I6.

Hope this helps.

Pete

Worked great, thanks for the assist.

You're welcome - glad it worked for you.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete