excel - Copy every nth line from one sheet to another

Question

I have an Excel spreadsheet with 1 column, 700 rows. I care about every seventh line. I don't want to have to go in and delete the 6 rows between each row I care about. So my solution was to create another sheet and specify a reference to each cell I want.

=sheet1!a1
=sheet1!a8
=sheet1!a15

But I don't want to type in each of these formulas ... `100 times.I thought if I selected the three and dragged the box around, it would understand what I was trying to do, but no luck.

Any ideas on how to do this elegantly/efficiently?

Answer 1

In A1 of your new sheet, put this:

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

... and copy down. If you start somewhere other than row 1, change ROW() to ROW(A1) or some other cell on row 1, then copy down again.

If you want to copy the nth line but multiple columns, use the formula:

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

This can be copied right too.