Referencing data from a row in a grid

[Josiah]

I have a row of data that is 128 columns wide (AZ5:FW5). I want to use that data in a grid 4 columns by 32 rows so that:

H5=AZ5, I5=BA5, J5=BB5, K5=BC5
H6=BD5, I6=BE5, J6=BF5, K6=BG5
...
...
...
and so on all the way down to H36 - K36


Is there a quick and easy way to do this without copying the data row by row?


(I probably could have already had it done by the time I typed this but it's the principle of the matter! )

 

[Snakehips]

Josiah,

In H5 use... =OFFSET(AZ$5,0,(ROWS(H$5:H5)-1)*4)

Drag to K5 then drag all 4 down to row 36

If you then want hard values rather than formulas in H5:K36 then select H5:K36 and do Copy / Paste Special ? Values

Hope that helps.

 

[Josiah]

Josiah,

In H5 use... =OFFSET(AZ$5,0,(ROWS(H$5:H5)-1)*4)

Drag to K5 then drag all 4 down to row 36

If you then want hard values rather than formulas in H5:K36 then select H5:K36 and do Copy / Paste Special ? Values

Hope that helps.

Thanks! That's perfect just like it is!

 

[Snakehips]

You are welcome.

It probably does not matter in your instance but you could use the below alternative if there was need to avoid using the 'volatile' OFFSET function.

=INDEX($AZ$5:$FW$5,1,((ROWS(H$5:H5)-1)*4)+COLUMNS($H5:H5))