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

  • Thread starter Thread starter MikeB
  • Start date Start date
M

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.
 
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
 
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.
 
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
Click to expand...
Thanks, Pete!
 
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.
Click to expand...

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