Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Adding Rows offsets to working rows across two worksheets
Excel 2003 SP2
I've just setup a spreadsheet that has 2 worksheets, Sheet1 and Sheet2. I've copied/pasted some data into Sheet1. Now in Sheet2 I parse the data that's in Sheet1. Sheet1 contains multiple GROUPS of data that are 23 rows. Hence, rows A1-A23 are Group 1 and rows A24-A46 are Group 2, rows A47-A69 are Group 3, etc. I'm parsing the data in Sheet2 to be Group 1 as row 1; Group 2 as row 2 and Group 3 as row 3. [Each row of any related Group from Sheet1 goes into a COLUMN in Sheet2.] That is, Sheet1!A1 is mapped to Sheet2!A1; Sheet1!A2 is mapped to Sheet2!B1; Sheet1!A3 is mapped to Sheet2!C1 etc. Now for the next Sheet2 row, I should have Sheet1!A24 is mapped to Sheet2!A2; Sheet1!A25 is mapped to Sheet2!B2; Sheet1!A26 is mapped to Sheet!C2 etc. What I don't know how to do is while in Sheet2 when I copy the Sheet1!A1 row it automatically becomes Sheet1!A2 rather than Sheet1!A24. ===So, how can I copy the Sheet2 A1 row to the Sheet2 A2 row and have the formulas change from Sheet1!A1 to Sheet1!A24? I need to change the 'add 1 row' to 'add 23 row' on the copy/paste. How can I do this? TIA Tom |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Adding Rows offsets to working rows across two worksheets
In Sheet2,
Put in A1: =INDEX(Sheet1!$A:$A,ROW(A1)*23-23+COLUMN(A1)) Copy A1 to W1, fill down until zeros appear signalling exhaustion of data That should extract the data from Sheet1 in exactly the manner you want -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tom" wrote: Excel 2003 SP2 I've just setup a spreadsheet that has 2 worksheets, Sheet1 and Sheet2. I've copied/pasted some data into Sheet1. Now in Sheet2 I parse the data that's in Sheet1. Sheet1 contains multiple GROUPS of data that are 23 rows. Hence, rows A1-A23 are Group 1 and rows A24-A46 are Group 2, rows A47-A69 are Group 3, etc. I'm parsing the data in Sheet2 to be Group 1 as row 1; Group 2 as row 2 and Group 3 as row 3. [Each row of any related Group from Sheet1 goes into a COLUMN in Sheet2.] That is, Sheet1!A1 is mapped to Sheet2!A1; Sheet1!A2 is mapped to Sheet2!B1; Sheet1!A3 is mapped to Sheet2!C1 etc. Now for the next Sheet2 row, I should have Sheet1!A24 is mapped to Sheet2!A2; Sheet1!A25 is mapped to Sheet2!B2; Sheet1!A26 is mapped to Sheet!C2 etc. What I don't know how to do is while in Sheet2 when I copy the Sheet1!A1 row it automatically becomes Sheet1!A2 rather than Sheet1!A24. ===So, how can I copy the Sheet2 A1 row to the Sheet2 A2 row and have the formulas change from Sheet1!A1 to Sheet1!A24? I need to change the 'add 1 row' to 'add 23 row' on the copy/paste. How can I do this? TIA Tom |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Adding Rows offsets to working rows across two worksheets
Hi Max,
This was PERFECT! Thanks! I've never used the INDEX function. Now I can parse to my hearts desire! WHere did you learn your tricks and get your knowledge? Is there a site or book or course you'd suggest? Thanks again!!! Tom "Max" wrote: In Sheet2, Put in A1: =INDEX(Sheet1!$A:$A,ROW(A1)*23-23+COLUMN(A1)) Copy A1 to W1, fill down until zeros appear signalling exhaustion of data That should extract the data from Sheet1 in exactly the manner you want -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tom" wrote: Excel 2003 SP2 I've just setup a spreadsheet that has 2 worksheets, Sheet1 and Sheet2. I've copied/pasted some data into Sheet1. Now in Sheet2 I parse the data that's in Sheet1. Sheet1 contains multiple GROUPS of data that are 23 rows. Hence, rows A1-A23 are Group 1 and rows A24-A46 are Group 2, rows A47-A69 are Group 3, etc. I'm parsing the data in Sheet2 to be Group 1 as row 1; Group 2 as row 2 and Group 3 as row 3. [Each row of any related Group from Sheet1 goes into a COLUMN in Sheet2.] That is, Sheet1!A1 is mapped to Sheet2!A1; Sheet1!A2 is mapped to Sheet2!B1; Sheet1!A3 is mapped to Sheet2!C1 etc. Now for the next Sheet2 row, I should have Sheet1!A24 is mapped to Sheet2!A2; Sheet1!A25 is mapped to Sheet2!B2; Sheet1!A26 is mapped to Sheet!C2 etc. What I don't know how to do is while in Sheet2 when I copy the Sheet1!A1 row it automatically becomes Sheet1!A2 rather than Sheet1!A24. ===So, how can I copy the Sheet2 A1 row to the Sheet2 A2 row and have the formulas change from Sheet1!A1 to Sheet1!A24? I need to change the 'add 1 row' to 'add 23 row' on the copy/paste. How can I do this? TIA Tom |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Adding Rows offsets to working rows across two worksheets
"Tom" wrote:
Hi Max, This was PERFECT! Thanks! I've never used the INDEX function. Now I can parse to my hearts desire! Glad it worked out good for you ! Where did you learn your tricks and get your knowledge? Ahh, that's a trade secret <g. I've worked a bit here & there and learnt much by reading/trying out the responses given by many great responders* in the various excel newsgroups, such as (in no particular order): microsoft.public.excel.worksheet.functions microsoft.public.excel.misc microsoft.public.excel.newusers microsoft.public.excel.programming Is there a site or book or course you'd suggest? For starters, try Debra Dalgleish's: http://www.contextures.com/tiptech.html Her excel book list page at http://www.contextures.com/xlbooks.html is one of the most comprehensive & updated that I know of *A list of regular responders is available at Debra's: http://www.contextures.com/xlngstats.html -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding rows in several worksheets along with formulas | Excel Discussion (Misc queries) | |||
inserting rows in linked worksheets | Excel Discussion (Misc queries) | |||
Hyperlinks doing weird things when adding columns or rows | Excel Worksheet Functions | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
Adding Rows to Master Sheet | New Users to Excel |