Every "n'th" Row

R

RyanR8881

How do I set a formula to choose every "n'th" row and extract it to another
spreadsheet?
 
G

Gord Dibben

On the "other" sheet enter this in A1

=OFFSET(Sheet1!$A$1,10*ROW()-10,0)

Drag/copy down as far as you wish.

Will return values from row 1, 11, 21, 31, 41 etc. from Sheet1

Adjust to suit.


Gord Dibben MS Excel MVP
 
M

Max

RyanR8881 said:
How do I set a formula to choose every "n'th" row
and extract it to another spreadsheet?
Click to expand...

Assume source data in Sheet1, cols A to C, data from row1 down

In say Sheet2,
you could place this in any start cell, say in B2:
=INDEX(Sheet1!A:A,ROWS($1:1)*2-1)
Copy B2 across by 3 cols to D2, fill down as far as required
to return rows: 1,3,5, ... from Sheet1

And if you wanted to return rows: 2,4,6, ... from Sheet1 instead,
use this in the start cell:
=INDEX(Sheet1!A:A,ROWS($1:1)*2)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400 Files:361 Subscribers:58
xdemechanik
---
 
Top