How to fill a column of cells on sheet1 with non adjacent cells on sheet2

[erj4443]

Hello! I have an excel file with multiple worksheets. Sheet1 has my report and sheet2 has a report I am pasting in from another source. On sheet1 I would like to fill 90 adjacent cells, E5:E94, with information from sheet2. The information on sheet2 that I would like Sheet1 to reference starts at D5 and goes up 6 each time all the way to D539. So...

E5=Sheet2!D5
E6=Sheet2!D11
E7=Sheet2!D17
Etc.

When I try to use the fill handle to fill in the remainder, E8:E94, excel does not reference the correct cells. I would rather not enter in each one manually and I'm sure it's a simple fix, I'm just a newb when it comes to excel. Please help!!

[Pete_UK]

Put this in E5 of Sheet1:

=INDEX(Sheet2!D:D,ROWS($1:1)*6-1)

then you can copy this down to E94

Hope this helps.

Pete

[erj4443]

That's amazing! Thank you! You have already done more than enough so please do not feel obligated to, but would you mind explaining how this formula works? I would greatly appreciate it!

[Pete_UK]

The term ROWS($1:1)*6-1 will initially return 5 (i.e. 1*6 - 1), but when it is copied onto the next row it will become ROWS($1:2)*6-1, which will evaluate to 11 (2*6 - 1), and then on the next row it becomes ROWS($1:3)*6-1, which is 17, and so on. These are the cell numbers in column D where you want to retrieve the data from, and the INDEX function allows you to do that.

Hope this helps.

Pete

[erj4443]

Genius! Thank you again! That saved me a ton of time