formula to copy contents of a column of cells into a row?

[MikeB]

I'm creating a list of people that play a round-robing game against
one another. I would like to have the top row of names automatically
copy from the column of names, but I cannot seem to devise a formula
that does this. It's hard to explain, but perhaps this example will
help.

If I have players

John,
Pete
Mary
Sally

I would like to build a table that lookas as follows:

------ John Pete Mary Sally
John -----
Pete ------
Mary ------
Sally ------

And I want to create a formule fo rthe top row so that is I add
another name in the column of names, another row will be populated.

Thanks.

 

[Pete_UK]

Put this in B1:

=INDEX($A2:$A5,COLUMN(A1))

then copy into C1:E1.

If you have more names then you will need to extend the range, or you
could use this instead in B1:

=INDEX($A:$A,COLUMN())

and copy across as far as you need. NOTE: you will get 0 showing up if
you have empty cells in column A, so don't copy it too far.

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

Suppose your column of names is in A2:A8 the easiest way to do what you want
is to highlight A2:A8, click Copy, move to B1 and choose Edit, Paste Special,
Transpose.

If you want a formula approach high light an equal number of cells on row 1
and type, but do not enter, the formula
=TRANSPOSE($A$2:$A$8)
Press Shift+Ctrl+Enter

This is an array function and to work properly must be entered this way.

You can also use INDIRECT, INDEX, or OFFSET.

 

[MikeB]

Put this in B1:

=INDEX($A2:$A5,COLUMN(A1))

then copy into C1:E1.

If you have more names then you will need to extend the range, or you
could use this instead in B1:

=INDEX($A:$A,COLUMN())

and copy across as far as you need. NOTE: you will get 0 showing up if
you have empty cells in column A, so don't copy it too far.

Hope this helps.

Pete

Thanks, Pete!

 

[MikeB]

Hi,

Suppose your column of names is in A2:A8 the easiest way to do what you want
is to highlight A2:A8, click Copy, move to B1 and choose Edit, Paste Special,
Transpose.

If you want a formula approach high light an equal number of cells on row1
and type, but do not enter, the formula
=TRANSPOSE($A$2:$A$8)
Press Shift+Ctrl+Enter

This is an array function and to work properly must be entered this way.

You can also use INDIRECT, INDEX, or OFFSET.

Thanks Shane. Sorry, but I don't know what Yes button you want me to
press?