Copy from another sheet but spreading the data out down the col

[Peter]

I need to rearrange some data from one worksheet into another.
For instance if A2 - A10 were numbers 2-10 I would like them copied into the
second worksheet in A2,A5,A8 ( so A2=1, A5 =2, A8=3..) If B1-B10 were also
numbers I would like them in A3,A6,A9. (from one worksheet to the other) So I
would like to set up the first 3 rows with formulas linking back to worksheet
1 from 3 different colums and then use the filldown function with results
occurring sequentially, at them moment it is missing values as I fill down,,
hope someone can help!

 

[RagDyer]

If I understand what you're looking to do,
say you have data on Sheet2,
from A1 to C30.

You want to copy this data to Sheet1, in Column A,
alternating the columns from Sheet2,
so that Column A on Sheet1 will look like:

A1
B1
C1
A2
B2
C2
A3
.... etc.

If that be the case, starting with data on Sheet2, from A1 to C30,
enter this formula *anywhere* you wish to start on Sheet1,
and copy down:

=INDEX(Sheet2!$A$1:$C$30,ROWS($1:3)/3,MOD(ROWS($1:1)-1,3)+1)

 

[Peter]

Hi RagDyer
Thanks for the formula I have put this in my sheet and with a few
modifications is working well.

Essentially I now need a formula for converting back. Say my data occurs
every 4th row in column A ie A3, A7, A11 etc... I would like to put this into
sheet 3 but so that the results are one after the other in that column. Ie:
A1=A3.A2=A7,,, if I have a formula that will work with fill down that would
be great.

Cheers
Peter

 

[Ragdyer]

Data on Sheet1, going to Sheet "whatever".
Data in Column A, where you want to start with
A3,
and then, every 4th row.

Enter this formula *wherever* you wish to start, and copy down as needed:

=INDEX(Sheet1!A:A,4*ROWS($1:1)-1)