Changing formulas to move multiple columns over but reference a single column over

Hi all,

I have attached an example of what I am trying to do.

I would run a simulation for a number of rooms and the results for each room within the simulation would be copied together and inserted into the sheet Solar Values. In the Sheet Solar Gain, column G takes data from the sheet Solar Values. For example column G takes the name of the room and the solar gain in kW from column C in Solar Values.

What I am wanting to know is, is it possible to copy the structure from column E through to K to column M but having the column that references C in Sheet Solar Values to reference column D e.g.) copy the cells over 6 columns but have it reference only 1column over?


Column A and C in Sheet Solar Gain takes the name from each room (G2) and the calculated solar gain (K3).
Is it also possible to copy the formula in cell A2 to A3 but having it reference O3 instead of G3, e.g) copy the cell down one row but have it reference a cell 8 columns over?

In the simulation there could be up to 200 rooms, and it would be tedious to do this manually for each room.
Your help would be gratefully welcomed

Regards,
Stephen

You can put this formula in G4:

=INDEX('Solar Values'!$C$4:$G$23,ROWS($1:1),MATCH(G$2,'Solar Values'!$C$2:$G$2,0))

then copy it down. Then put this formula in cell G2:

=INDEX('Solar Values'!$C$2:$G$2,INT((COLUMNS($G:G)-1)/8)+1)

Change the formula in A2 to this:

=INDEX($G$2:$BA$2,(ROWS($1:1)-1)*8+1)

then copy this down, and fill in appropriate values in column B.

Then you can copy the block of cells in columns E:K across into M:S and into U:AA and so on.

Hope this helps.

Pete