Thread: Column Headers to Column Data

Assuming you are entering the data in row 2:

Code:

=OFFSET($A$1,INT((ROW()-2)/3)+1,0) & " " & OFFSET($A$1,0,MOD(ROW()-2,3)+1) & " " & OFFSET($A$1,INT((ROW()-2)/3)+1,MOD(ROW()-2,3)+1)

Change the -2 to the number of the row of your first output line.
Change the 3 to the number of header lines.
Change the $A$1's to the upper left corner of your table.

=OFFSET($A$1,INT((ROW()-2)/3)+1,0) & " " & OFFSET($A$1,0,MOD(ROW()-2,3)+1) & " " & OFFSET($A$1,INT((ROW()-2)/3)+1,MOD(ROW()-2,3)+1)

Works like a charm Mdbct! Thanks a million.

Can I then separate each component into a cell by themselves?

Sure. Put each Offset into it's own column.

Name

Code:

=OFFSET($A$1,INT((ROW()-2)/3)+1,0)

Fruit

Code:

OFFSET($A$1,0,MOD(ROW()-2,3)+1)

Value

Code:

OFFSET($A$1,INT((ROW()-2)/3)+1,MOD(ROW()-2,3)+1)

Many, many thanks! Works perfectly.