TRANSPOSE()
 

Friends,

I know how to copy / paste special / transpose, but I cannot get the
TRANSPOSE() worksheet function to work. Let's say I have 1,2,3 in the
horizontal array A1:C1. What, exactly, do I do to transpose the 1,2,3 into a
vertical array in (A1:A5), using only the TRANSPOSE() worksheet function.

I keep getting the #Value# error, so I must be missing something.

Thanks for your help ...

Bill Morgan

Highlight A3:A5

Enter formula

=Transpose(A1:C1)

It is an array function so enter while holding down CTRL+SHIFT+ENTER



"bill_morgan_3333" wrote in
message ...
Friends,

I know how to copy / paste special / transpose, but I cannot get the
TRANSPOSE() worksheet function to work. Let's say I have 1,2,3 in the
horizontal array A1:C1. What, exactly, do I do to transpose the 1,2,3 into
a
vertical array in (A1:A5), using only the TRANSPOSE() worksheet function.

I keep getting the #Value# error, so I must be missing something.

Thanks for your help ...

Bill Morgan

Paul has given you the steps, but there are 2 additional points.

The range containing the transposed data cannot overlap the source. You have
data in A1. You can't put a the TRANSPOSE formula there without overwriting
the 1st data point.

Normally the two ranges should be the same size. Yours aren't. The source is 3
cells, the destination is 5 cells. If you put the formula in A1:A6, you'll see
#NA in the 2 extra cells. If the destination range is smaller, you won't see
all of the data.

You could, however, put this formula in A2:A3 =TRANSPOSE(B1:C1) which will
leave the data in A1 alone and put the data from the other two cells below it.

Remember, as Paul said, this is an array formula.

On Wed, 3 Nov 2004 20:49:01 -0800, "bill_morgan_3333"
wrote:

Friends,

I know how to copy / paste special / transpose, but I cannot get the
TRANSPOSE() worksheet function to work. Let's say I have 1,2,3 in the
horizontal array A1:C1. What, exactly, do I do to transpose the 1,2,3 into a
vertical array in (A1:A5), using only the TRANSPOSE() worksheet function.

I keep getting the #Value# error, so I must be missing something.

Thanks for your help ...

Bill Morgan

Paul,

Thank you for that concise, logical instruction. It worked perfectly.

"Paul Corrado" wrote:

Highlight A3:A5

Enter formula

=Transpose(A1:C1)

It is an array function so enter while holding down CTRL+SHIFT+ENTER



"bill_morgan_3333" wrote in
message ...
Friends,

I know how to copy / paste special / transpose, but I cannot get the
TRANSPOSE() worksheet function to work. Let's say I have 1,2,3 in the
horizontal array A1:C1. What, exactly, do I do to transpose the 1,2,3 into
a
vertical array in (A1:A5), using only the TRANSPOSE() worksheet function.

I keep getting the #Value# error, so I must be missing something.

Thanks for your help ...

Bill Morgan

Myrna,

Thanks for the clarification. I meant to say "transpose to A3:A5", so that
was a glitch on my part. Between your answer and Paul's I'm now clear on
TRANSPOSE(). Thanks again ...

"Myrna Larson" wrote:

Paul has given you the steps, but there are 2 additional points.

The range containing the transposed data cannot overlap the source. You have
data in A1. You can't put a the TRANSPOSE formula there without overwriting
the 1st data point.

Normally the two ranges should be the same size. Yours aren't. The source is 3
cells, the destination is 5 cells. If you put the formula in A1:A6, you'll see
#NA in the 2 extra cells. If the destination range is smaller, you won't see
all of the data.

You could, however, put this formula in A2:A3 =TRANSPOSE(B1:C1) which will
leave the data in A1 alone and put the data from the other two cells below it.

Remember, as Paul said, this is an array formula.

On Wed, 3 Nov 2004 20:49:01 -0800, "bill_morgan_3333"
wrote:

Friends,

I know how to copy / paste special / transpose, but I cannot get the
TRANSPOSE() worksheet function to work. Let's say I have 1,2,3 in the
horizontal array A1:C1. What, exactly, do I do to transpose the 1,2,3 into a
vertical array in (A1:A5), using only the TRANSPOSE() worksheet function.

I keep getting the #Value# error, so I must be missing something.

Thanks for your help ...

Bill Morgan