Adding Rows offsets to working rows across two worksheets

[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

 

[Guest]

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

 

[Guest]

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

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

---

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

 

[Guest]

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

---