make column values column headers

G

Guest

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
 
G

Guest

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.
 
G

Guest

the lists are on seperate worksheets. how do i refer to another sheet. this
is what i'm trying to do =OFFSET(ODBC$A2,COLUMN(B1)-COLUMN($B1),0). am i
close to the amrk, in my placement for the sheet name?

JLatham said:
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
 
G

Guest

Very close, it would look like this:
=OFFSET(ODBC!$A2,COLUMN(B1)-COLUMN($B1),0)
if a sheet name has spaces in it, the name needs to be in single quotes like:
=OFFSET('OD BC'!$A2,COLUMN(B1)-COLUMN($B1),0)

Hope that works out for you.
dunskii said:
the lists are on seperate worksheets. how do i refer to another sheet. this
is what i'm trying to do =OFFSET(ODBC$A2,COLUMN(B1)-COLUMN($B1),0). am i
close to the amrk, in my placement for the sheet name?

JLatham said:
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
 
G

Guest

Thanks JL,Works a treat, though if a row is deleted from the list, I get a
#ref error, is there a way to stop this?

JLatham said:
Very close, it would look like this:
=OFFSET(ODBC!$A2,COLUMN(B1)-COLUMN($B1),0)
if a sheet name has spaces in it, the name needs to be in single quotes like:
=OFFSET('OD BC'!$A2,COLUMN(B1)-COLUMN($B1),0)

Hope that works out for you.
dunskii said:
the lists are on seperate worksheets. how do i refer to another sheet. this
is what i'm trying to do =OFFSET(ODBC$A2,COLUMN(B1)-COLUMN($B1),0). am i
close to the amrk, in my placement for the sheet name?

JLatham said:
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.


:

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
 
G

Guest

Yep, sure can!

=IF(ISERROR(OFFSET(ODBC!$A2,COLUMN(B1)-COLUMN($B1),0)),"",OFFSET(ODBC!$A2,COLUMN(B1)-COLUMN($B1),0))


dunskii said:
Thanks JL,Works a treat, though if a row is deleted from the list, I get a
#ref error, is there a way to stop this?

JLatham said:
Very close, it would look like this:
=OFFSET(ODBC!$A2,COLUMN(B1)-COLUMN($B1),0)
if a sheet name has spaces in it, the name needs to be in single quotes like:
=OFFSET('OD BC'!$A2,COLUMN(B1)-COLUMN($B1),0)

Hope that works out for you.
dunskii said:
the lists are on seperate worksheets. how do i refer to another sheet. this
is what i'm trying to do =OFFSET(ODBC$A2,COLUMN(B1)-COLUMN($B1),0). am i
close to the amrk, in my placement for the sheet name?

:

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.


:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top