Adding Rows offsets to working rows across two worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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 said:
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

---
Tom said:
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
Click to expand...
Click to expand...
 
Tom said:
Hi Max,
This was PERFECT! Thanks! I've never used the INDEX function. Now I can
parse to my hearts desire!
Click to expand...

Glad it worked out good for you !
Where did you learn your tricks and get your knowledge?
Click to expand...

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?
Click to expand...

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

---
 
Back
Top