return every 3rd row

[jammoca]

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

 

[T. Valko]

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
1	Name1
2	_
3	_
4	Name2
5	_
6	_
7	Name3
8	_
9	_
10	Name4
11	_
12	_
13	Name5
14	_
15	_
16	Name6
17	_
18	_
19	Name7
Sheet2

 

[HarryS]

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

or somewhere about the above

 

[pagrender]

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

 

[pagrender]

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)