Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy from another sheet but spreading the data out down the col
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy from another sheet but spreading the data out down the col
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) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peter" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy from another sheet but spreading the data out down the co
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" wrote: 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) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peter" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy from another sheet but spreading the data out down the co
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) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peter" wrote in message ... 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" wrote: 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) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Peter" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data of two cells from Sheet 2 into one cell in Sheet 1 | Excel Worksheet Functions | |||
Copy/filter data in a sheet from another sheet | Excel Discussion (Misc queries) | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
how to copy a cell with formula from sheet 1 (data is all vertical) into sheet 2 | Excel Worksheet Functions | |||
Copy sheet 1 data to sheet 2 cells. | Excel Worksheet Functions |