| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| need to Copy or Move to active cell from specified range | Excel Discussion (Misc queries) | |||
| AUTOMATIC way to copy the value of a cell in one spreadsheet | Excel Worksheet Functions | |||
| How to Copy the value of a cell to any given cell | Excel Discussion (Misc queries) | |||
| hpw do I logic test a cell then copy the row to diff. SS | Excel Worksheet Functions | |||
Linear Mode
