There are at least two ways to do this. First method is a simple copy and
paste using a featue that 'rotates' or transposes the data by 90 degrees:
vertical column becomes horizontal layout for this operation:
Highlight the entries in your list and use Edit | Copy then go to the first
cell where you want them to appear across the top of the matrix as labels and
use
Edit | Paste Special and check the [Transpose] option. Done.
Repeat for the second list.
The second method is linking using the OFFSET() function and a trick with
column numbers to do the transposing. This will allow the list information
to change and have the matrix headers update automatically.
Assume that the lists and matrix are on the same sheet, but this will work
across sheets.
Assume that the information in the first list starts at cell A2 and goes
down the sheet.
Your matrix starts at F2 and you want the headers to run across thru G2, H2,
etc
In F2 you would put this formula:
=OFFSET($A2,Column(F2)-Column($F2),0)
and fill that formula across row 2 into columns G, H, I, etc.
Lets say that the second list starts on down the sheet at A44 and you need
those to continue where the first list stopped at the top of your matrix,
which we will say was at I2, so J2 is where you'd start with this formula:
=OFFSET($A44,Column(J2)-Column($J2),0)
and again fill it out to the right.
dunskii said:
hello all,
I have 3 sheets in my workbook. 2 lists and 1 matrix. Is there a way to
make the values of a column in 1 of my lists to be the values of a row (the
column headers) in the matrix. I can link the values from 1 column to
another but not a column to a row.
thanks in advance,
d