transferring columns to rows

[orsm6]

Hi all - I am not even sure how to ask this, so I will do my best.

on one sheet i have data created by formulas and is organised as columns.
on another sheet i want to return the same data in the columns but organised as rows.

the only way i know how (simply) is to do =sheetA1 as example for ever cell, but when i drag the formula across it obviously moves columns not down rows. is there an easier way?

in my images, source data is the data formatted into columns.
in the result image i have manually done the formulas, but if i then drag them to the right it returns the wrong values as you can see in yellow. they should match the columns in source

TIA

 

[BigBeachBananas]

Can you share what's the desired outcome?

 

[orsm6]

Can you share what's the desired outcome?

Hi Bananas. Thanks for your response.

the data is to display the HOME and AWATY games for my sport club.
referring to the images:
- source: if there are enough teams, the blank cells could be populated with a team number.
- roster: I am trying to find an easy way to essentially "lookup" the weekly roster in the column layout, but transfer it over to another sheet in a row layout

 

[BigBeachBananas]

Try this. Adjust the cell references as needed.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
4	1	11		1	2	3	4	5	6	7	8	9	10
5	2	12		11	12	13	14	15	16	17	18	19	20
6	3	13
7	4	14
8	5	15
9	6	16
10	7	17
11	8	18
12	9	19
13	10	20
14
Sheet2

Cell Formulas
Range	Formula
D4:M5	D4	=WRAPROWS(VSTACK(A4:A13,B4:B13),10)
Dynamic array formulas.

 

[orsm6]

Try this. Adjust the cell references as needed.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
4	1	11		1	2	3	4	5	6	7	8	9	10
5	2	12		11	12	13	14	15	16	17	18	19	20
6	3	13
7	4	14
8	5	15
9	6	16
10	7	17
11	8	18
12	9	19
13	10	20
14
Sheet2

Cell Formulas
Range	Formula
D4:M5	D4	=WRAPROWS(VSTACK(A4:A13,B4:B13),10)
Dynamic array formulas.

LEGEND!!

works great, thank you!!

 

[BigBeachBananas]

You're welcome. Glad to help.