Excel 2013 copy consecutive cells to non-consecutive cells

[ECEE3]

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.

 

[bbott]

Welcome to the board.

Try this in A1 on Sheet2, copy and paste as needed:

=INDEX(Sheet1!A:A,INT(ROW(A1)/14)+1,1)

 

[ECEE3]

Thanks for the help.

 

[bbott]

Thanks for the help.

You're welcome.