# Return value of every nth column

#### delta_negative

##### New Member
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.

Last edited:

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try this in F1 and filled down/right as far as needed.

=INDEX(A:A,(ROWS(A\$1:A1)-1)*5+1)

Don't change the A\$1:A1 reference, it has no relationship with your data.
The *5 represents the interval (every 5 rows)
The +1 represents the Row# to begin in.

Try this in F1 and filled down/right as far as needed.

=INDEX(A:A,(ROWS(A\$1:A1)-1)*5+1)

Don't change the A\$1:A1 reference, it has no relationship with your data.
The *5 represents the interval (every 5 rows)
The +1 represents the Row# to begin in.

That works absolutely superb. Thank you very much.

I edited the original post because I had forgotten to add that in row 1 column G has a formula MAX(B1:B5) and column H has the formula MIN(C1:C5) with each successive iteration incrementing MAX(B6:B10) and MIN(C6:C10),etc. Is it possible to add that functionality to the formula you posted?

Last edited:
Try

=MAX(OFFSET(B1:B5),ROWS(A\$1:A1)-1)*5,0)
and
=MIN(OFFSET(C1:C5),ROWS(A\$1:A1)-1)*5,0)

Try

=MAX(OFFSET(B1:B5),ROWS(A\$1:A1)-1)*5,0)
and
=MIN(OFFSET(C1:C5),ROWS(A\$1:A1)-1)*5,0)

Received error message"You have entered too few arguments for the function" with B5 highlighted in the formula bar.

I messed up my parens,
Also forgot to lock the row references..

that's what I get for not testing

=MAX(OFFSET(B\$1:B\$5,(ROWS(A\$1:A1)-1)*5,0))
and
=MIN(OFFSET(C\$1:C\$5,(ROWS(A\$1:A1)-1)*5,0))

I messed up my parens,
Also forgot to lock the row references..

that's what I get for not testing

=MAX(OFFSET(B\$1:B\$5,(ROWS(A\$1:A1)-1)*5,0))
and
=MIN(OFFSET(C\$1:C\$5,(ROWS(A\$1:A1)-1)*5,0))

Really appreciate your help. That's exactly what I needed.

Just realized there is 1 missing part, if you have the time. I am trying to SUM the totals of the same columns, rows 1-5, 6-10, etc as well as rows 1-15, 16-30, etc. I attempted to modify your code but no go.

You might just change the word MAX to say..... SUM

And the *5 represents the interval (every n rows)
and adjust the initial C1:C5 range accordingly

You might just change the word MAX to say..... SUM

And the *5 represents the interval (every n rows)
and adjust the initial C1:C5 range accordingly

Another home run.

Greatly appreciated. Hope you have a great weekend.

You might just change the word MAX to say..... SUM

And the *5 represents the interval (every n rows)
and adjust the initial C1:C5 range accordingly

After running the suggested code for a few days everything is working great. However I now realize that because the size of the worksheet is dynamic, the processor usage is rapidly increasing to the point of consuming gigs of ram.

Have researched the problem and MS suggest different strategies to improve the situation. I think in this instance a huge step would be to limit the first calculation to execute every 60 seconds. I have added a row that contains the time in format h:mm:ss but have no idea how to reference that in conjunction with the code you have already supplied so that the code executes every time the time cell equals 59, on a continual iterative basis. Is such a thing possible?

Any help always very much appreciated .

Replies
8
Views
231
Replies
3
Views
333
Replies
1
Views
424
Replies
4
Views
270
Replies
2
Views
545

1,203,069
Messages
6,053,352
Members
444,655
Latest member
didr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back