copy offset to cell
 

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
--
....kev...

copy offset to cell
 

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!
================================================== ===

"kev" wrote in message
...
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
--
....kev...

copy offset to cell
 

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.
--
....kev...


"RagDyeR" wrote:

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!
================================================== ===

"kev" wrote in message
...
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
--
....kev...

copy offset to cell
 

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!
================================================== ===

"kev" wrote in message
...
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.
--
....kev...


"RagDyeR" wrote:

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!
================================================== ===

"kev" wrote in message
...
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
--
....kev...

copy offset to cell
 

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

"RagDyeR" wrote in message
...
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!
================================================== ===

"kev" wrote in message
...
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.
--
....kev...


"RagDyeR" wrote:

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!
================================================== ===

"kev" wrote in message
...
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
--
....kev...

copy offset to cell
 

THANKS a mil, I can keep my hair now :)

--
....kev...


"RagDyeR" wrote:

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

"RagDyeR" wrote in message
...
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!
================================================== ===

"kev" wrote in message
...
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.
--
....kev...


"RagDyeR" wrote:

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!
================================================== ===

"kev" wrote in message
...
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
--
....kev...

copy offset to cell
 

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

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"kev" wrote in message
...
THANKS a mil, I can keep my hair now :)

--
...kev...


"RagDyeR" wrote:

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 !

--------------------------------------------------------------------------
---------------------

"RagDyeR" wrote in message
...
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!
================================================== ===

"kev" wrote in message
...
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.
--
....kev...


"RagDyeR" wrote:

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!
================================================== ===

"kev" wrote in message
...
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
--
....kev...