By the way, if you'd just wanted the headers at the top of the columns and
nothing but the values (out of column M) below it you could have used this at
C7 (and fill across to J7)
=OFFSET(Sheet1!$L$3,(COLUMN()-3),0)
then in C8 (and initially filled right to J8) you could have put this:
=OFFSET(Sheet1!$M$3,(COLUMN()-3)+(((ROW()-8))*8),0)
Then you could fill the entire group on row 8 on down the sheet.

I gather from your change in /2 to /1 that this is probably what you ended
up wanting.

"cj2k2k" wrote:

I appreciate your help.
I don't understand how it works, but it works.
If is not too much trouble, I'm interested in learning who it works.
Could you break down the elements of the formula and what they do?


"JLatham" wrote:

You're welcome. I almost messed it up completely - didn't realize until a
little late in the game that it was grouped in repeating sets of 8 and that
you'd probably want to continue not just across the sheet, but down and put
each set of 8 on different row, and THEN working with 2 columns tossed yet
another twist in it. You're lucky I was well into my 3rd cup of morning
coffee when I went to work on that one!

"cj2k2k" wrote:

JLatham,

You are a genius!
I got it to work. I had to tweak the offset a little.

=OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-8)/1)*8),1)

Thanks a million!

"JLatham" wrote:

In C7
=OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-7)/2)*8),0)
in C8
=OFFSET(Sheet1!$M$3,(COLUMN()-3)+(((ROW()-8)/2)*8),0)

then just fill them both to the right over to column J.
When you fill down the sheet, make sure you select both C7 and C8 before
starting the fill down the sheet, that'll get Sheet1 row L11 into C9 and M11
into C10



"cj2k2k" wrote:

What formula do I need to traspose sets of data from Sheet1 to Sheet2?

the data comes in sets of 8, sheet1 looks like this (from L3)

L M

3 POCKET 1
4 DIE 1
5 CC-AUTO 12
6 CC-CALC 4
7 EB 3
8 CC-FLOW 6
9 EB-FLOW 1
10 Total Void 42
11 POCKET 2
12 DIE 1
13 CC-AUTO 23
14 CC-CALC 0
15 EB 2
16 CC-FLOW 12
17 EB-FLOW 2
18 Total Void 37

Sheet2 needs to look like this (from C7)

C D E F G H I J

7 POCKET Die CC-AUTO CC-CALC EB CC-FLOW EB-FLOW Total
Void
1 1 12 4 3 6 1 42
2 1 23 0 2 12 2 37


I want to look for a match the Headers on Sheet2
("POCKET","Die","CC-AUTO","CC-CALC","EB","CC-FLOW","EB-FLOW","Total Void") in
Sheet1, Column L3+ and get the value of Column M3+.

This is what I have working so far..

=OFFSET('Sheet1'!$L3,MATCH(C7,'Sheet2'!$L:$L,0)*0, 1)

How do I make this formula skip every 8 rows?

Thanks,
CJ

JLatham,

That was a great explanation indeed! I really appreciate the time you took
to put it together. Now I understand how it works.
You demonstrate great coacing and writing skills. Have you considered being
an author? If not, you should!
Thanks again,

CJ