Return value of every nth column

[delta_negative]

A worksheet has data from machine oil pressure readings in column range A:D. The need is for a formula or macro to perform the following;

Starting at row 1 copy the values of A:D to F:I every 5th row so that row 1 F:I will contain the values of row1 A:D, row 2 F:I will contain the values of row 6 A:D, row 3 F:I will contain the values row 11 A:D, etc. It would be great to have the same procedure followed every 15th row starting at row 1 K:N, then row 16, then row 31, etc. Sheet has 100s of Ks of rows so would it be possible to automatically perform this to the last row, even when new rows are being added?

Column G has formula MAX(B1:B5), Column H has formula MIN(C1:C5) with same formula in row6, row 11,etc.

Have been copying down formula but it is time consuming as well as a problem if I accidentally unclick with the mouse.

Any help greatly appreciated.

 

[Exceluser_78]

Hi,

I tried using this formula for my issue, but I think I am doing something wrong.
I have cumulative data from Jan to Dec in row 3 (horizontally). I need to pull the value of March, June, Sep and Dec (every quarter) in a different sheet , same workbook.
the formula INDEX(A:A,(ROWS(A$3:A3)-1)*3+1) did not return the value of March (I changed the 5 into 3). Grateful for any suggestions.

 

[Jonmo1]

Change +1 to +3

 

[Jonmo1]

Wait, you said it’s horizontal in row 3?

=INDEX(3:3,(COLUMNS($A3:A3)-1)*3+3

 

[Exceluser_78]

Hi, thanks so much for your reply.

I tried your suggestion:
INDEX(data!A3:A3,(COLUMNS(data!$A3:A3)-1)*3)+3

it did return the value of March, but when I copy to the right to extract the June value etc . I get an error #REF !

the values are in sheet " data" and I need to copy only the values of March/June/Sep/Dec to another sheet.

Cumulative since beginning of year
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2 4 5 7 9 14 17 22 22 24 26 34

Thanks...


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 106"]
<tbody>[TR]
[TD="width: 106"][TABLE="width: 106"]
<tbody>[TR]
[TD="width: 106"]INDEX(data!A3:A3,

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Jonmo1]

You changed 3:3 to A3:A3

 

[Exceluser_78]

You changed 3:3 to A3:A3

Ok, now I have this: INDEX(data!3:3,(COLUMNS(data!$A3:A3)-1)*3)+3.
I am not getting the correct value anymore and also strange numbers are appearing..
The values in sheet "data" are this:

[TABLE="class: grid, width: 15, align: left"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 8, align: right"]
<tbody>[TR]
[TD="align: center"]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD="align: right"]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]14[/TD]
[TD]17[/TD]
[TD]22[/TD]
[TD]22[/TD]
[TD]24[/TD]
[TD]26[/TD]
[TD]34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]






After applying the formula (this table is located in a different sheet) I get this result:
[TABLE="class: grid, width: 8, align: left"]
<tbody>[TR]
[TD]Mar[/TD]
[TD]Jun[/TD]
[TD]Sep[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8[/TD]
[TD]17[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]




while it should be this:
[TABLE="class: grid, width: 8, align: left"]
<tbody>[TR]
[TD]Mar[/TD]
[TD]Jun[/TD]
[TD]Sep[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]14[/TD]
[TD]22[/TD]
[TD]34[/TD]
[/TR]
</tbody>[/TABLE]




Thank you for your time, I really appreciate this. I am trying to figure this out for a very long time now.

 

[Exceluser_78]

somehow that formula is not working for me.

However I found something that does the trick, I have to test it on long series though:
[TABLE="width: 184"]
<tbody>[TR]
[TD="class: xl65, width: 184"]INDEX(data!$A$3:$L$3,COLUMN()-2)*3+3)[/TD]
[/TR]
</tbody>[/TABLE]

I don't know why this worked, especially the -2 ?

Thanks again..

 

[Jonmo1]

There's also a misplaced Paren )

INDEX(data!3:3,(COLUMNS(data!$A3:A3)-1)*3)+3

Should be
INDEX(data!3:3,(COLUMNS(data!$A3:A3)-1)*3+3)