# return every 3rd row

#### jammoca

##### Banned
I have a list of names in A1:A100 on 'Sheet1'.

I'd like to return the name from Sheet1!A1 to Sheet2!A1
Then I'd like Sheet1!A2 to appear in Sheet2!A4
Then Sheet1!A3 to appear in Sheet2!A7
etc etc etc

In other words, in the 2nd sheet, I'd like each name from the sheet1 list to appear 3 rows below the one before it.

How would I formulate this ?

Chris

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I have a list of names in A1:A100 on 'Sheet1'.

I'd like to return the name from Sheet1!A1 to Sheet2!A1
Then I'd like Sheet1!A2 to appear in Sheet2!A4
Then Sheet1!A3 to appear in Sheet2!A7
etc etc etc

In other words, in the 2nd sheet, I'd like each name from the sheet1 list to appear 3 rows below the one before it.

How would I formulate this ?

Chris
Maybe this...

On Sheet2...

Enter this formula in A1:

=Sheet1!A1

Enter this formula on A2 and copy down as needed:

=IF(MOD(ROWS(A\$2:A2),3),"",INDEX(Sheet1!A\$1:A\$100,COUNTIF(A\$1:A1,"?*")+1))

This is what the results will look like:

Book1
A
1Name1
2_
3_
4Name2
5_
6_
7Name3
8_
9_
10Name4
11_
12_
13Name5
14_
15_
16Name6
17_
18_
19Name7
Sheet2

for rr= 1 to 100
sheet("Sheet2").cells(3*rr-2,1)=sheets("sheet1").cells(rr,1)
next rr

Try this in A2 and copy down:
=INDIRECT("Sheet1!A"&COUNT(\$A\$1:A1)*3+MOD(3,2))

I guess I didn't need to have the MOD in there...

=INDIRECT("Sheet1!A"&COUNT(\$A\$1:A1)*3+1)

By the way, this only works when numbers are in A4, A7, etc... If you have values, you will need this formula:
=INDIRECT("Sheet1!A"&COUNTA(\$A\$1:A1)*3+1)

Replies
9
Views
240
Replies
2
Views
215
Replies
2
Views
230
Replies
11
Views
561
Replies
4
Views
106

1,196,057
Messages
6,013,164
Members
441,751
Latest member
336448

### 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