Want to make 1 Cell = 3 cells Vertically

[Drifter]

I am trying to make the following happen:

Original Data:
A B C
1 A1 B1 C1
2 A2 B2 C2

What I want to happen:

A B
1 A1 B1
2 A1 C1
3 A2 B2
4 A2 C2

Whenever I try to Copy the Formulae on new worksheet I get something like:

A B
1 A1 B1 (Direct reference of cells A1:B1 above)
2 A1 C1 (Direct reference of cells A1 and C1 above)
3 A3 B3 (Copying formulae from the 2 rows above to this row and
Row 4)
4 A3 C3

Basically 1 job used to be recorded (Column A) as 1 Column entry with
rate/hr for 2 different situations in the following 2 columns.
Now to put this into a database we want Job and Rate 1 on one row and Job
and Rate 2 on the next row.

Excel seems intent on counting these rows on new sheet, and then referencing
the relative count on the sheet being referenced. I can't force it to be
absolute, and still count + 1 rows on the original sheet when I move down 2
rows on the new sheet.

There is quite a bit of data (original rows, and columns with dependant are
actually 5), so finding something that duplicates one cell 5 times, and
references the cells after it in same row only once is important. I have
tried "vlookup" but still can't make it work the way I want.

thanks,
D

 

[Max]

One way

Assuming this is in Sheet1:

Original Data:
A B C
1 A1 B1 C1
2 A2 B2 C2

etc

In Sheet2
-------------
Put in A1:
=OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/2),)

Put in B1:
=OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/2),MOD(ROW(A1)-1,2)+1)

Select A1:B1, fill down to B4
(or beyond depending on the orig. data in Sheet1)

You'll get the desired results:

A B
1 A1 B1
2 A1 C1
3 A2 B2
4 A2 C2

etc