Formula for vertical to horizontal data

[nshoesmith]

I have data in E2-E1044 of Sheet 5 that I want to display in Sheet 1, shown below.

The data may change, so I want to show the cell rather than just copy and paste current cell contents. The data should go in Column E to Column K in this other sheet, then go onto the next line, as displayed below.

However, I don't want to have to enter =Sheet5!E2,=Sheet5!E etc that many times.

I've tried to find a formula to do this for me, but haven't managed to work out a way to do. I'm especially having trouble getting any formula to work from E to K then continuing on the next row.

Does anyone have any ideas?

Thanks in advance.

[#2]

@nshoesmith 

To make a dynamic reference, you will have to use INDIRECT, CONCATENATE and ROW to get the results you're looking for. See the sample workbook below of it is used.

Cells in yellow show three unique formula set ups to make it easy to drag and pull down the formulas. To simply pull the formulas down, you will need to select the entire row (example E3:K3).

[#3]

@nshoesmith 

Enter the following formula in E2:

=INDEX(Sheet5!$E:$E,(ROW()-ROW($E$2))*7+COLUMN()-COLUMN($E$2)+2)

Fill to the right to K2, then fill down.