copy offset to cell

[Guest]

Example: I want to copy the values of cells in sheet A to cells in sheet
B.The values in A are separated by at least 4 rows. Cells in B where I want
those values are separated by 1 row.
How can I copy the values without having to enter each cell reference
indivudually in B?
I know I can Insert "rows" to equal the difference then delete, but my data
consists of hundreds of "source" and "destination" files over several sheets
therefore making changing the ref in the formula about as easy.
Is there a formula to add columns or rows such as cell A1+3 = cell D1 or
cell A1 + 3 = cell A4?
Pulling my hair out!
Thank You

 

[RagDyeR]

To get the values of Sheet1, rows A1, A4, A8, A12, ...etc. into Sheet2, into
every consecutive row, try this anywhere on Sheet2, and copy down as needed:

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

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Example: I want to copy the values of cells in sheet A to cells in sheet
B.The values in A are separated by at least 4 rows. Cells in B where I want
those values are separated by 1 row.
How can I copy the values without having to enter each cell reference
indivudually in B?
I know I can Insert "rows" to equal the difference then delete, but my data
consists of hundreds of "source" and "destination" files over several sheets
therefore making changing the ref in the formula about as easy.
Is there a formula to add columns or rows such as cell A1+3 = cell D1 or
cell A1 + 3 = cell A4?
Pulling my hair out!
Thank You

 

[Guest]

That works "Great" for consecutive paste, Thank You, but one more quick
question:
How do I now make this work on the "destination" paste if I use lets say
every 2 rows. IE.... Source data (sheet1) is -7 rows apart and the
destination to paste (sheet2) is every 3 rows?
Quick note: All data differences are in either row OR column, never both at
the same time.
Once again Thank you for such a speedy and helpful responce.

 

[RagDyeR]

This will take A1, A7, A14, A21, ... etc. , and copy it to say D1, D3, D5,
D7, ... etc:

In D1 enter:

=INDEX(Sheet1!A:A,7*(ROWS($1:2)/2)-7)

And, in D3 enter:

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

NOW, select D1 to *D4*,

Click on the fill handle of this *4 cell* selection, and drag down to copy
as needed.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

That works "Great" for consecutive paste, Thank You, but one more quick
question:
How do I now make this work on the "destination" paste if I use lets say
every 2 rows. IE.... Source data (sheet1) is -7 rows apart and the
destination to paste (sheet2) is every 3 rows?
Quick note: All data differences are in either row OR column, never both at
the same time.
Once again Thank you for such a speedy and helpful responce.

 

[RagDyeR]

FWIW,

These 2 formulas can be entered *anywhere*, as long as you skip a row
between them, and copy down a *4 row* selection.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

This will take A1, A7, A14, A21, ... etc. , and copy it to say D1, D3, D5,
D7, ... etc:

In D1 enter:

=INDEX(Sheet1!A:A,7*(ROWS($1:2)/2)-7)

And, in D3 enter:

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

NOW, select D1 to *D4*,

Click on the fill handle of this *4 cell* selection, and drag down to copy
as needed.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

That works "Great" for consecutive paste, Thank You, but one more quick
question:
How do I now make this work on the "destination" paste if I use lets say
every 2 rows. IE.... Source data (sheet1) is -7 rows apart and the
destination to paste (sheet2) is every 3 rows?
Quick note: All data differences are in either row OR column, never both at
the same time.
Once again Thank you for such a speedy and helpful responce.

 

[Guest]

THANKS a mil, I can keep my hair now

 

[Ragdyer]

So I guess you'll have to continue buying combs now.<g>

Thanks for the feed-back.