Changing fill orientation

F

Ferrin444

I'm trying to get a table "transposed" by referencing the cells in another
worksheet. Currently it looks something like this

January February March April
M1
M2
M3
M4

I'd like it to look like this, while maintaining a dynamic link:

M1 M2 M3 M4 M5
January
February
March
April

I'd like to be able to reference the first cell and then drag the formula
either down (instead of rows incrementing, the columns would increment) and
the same for dragging the formula to the right. Is this possible????

ie
D1 D2 D3 D4
E1 E2 E3 E4
F1 F2 F3 F4

where D1 was the first reference brought in and the rest were dragged and
filled from there.

Can anyone help???
 
M

Myrna Larson

Use the TRANSPOSE function. Let's say your table is in Sheet1!$C$23:$N$38.
That's 16 rows by 12 columns.

On the 2nd sheet, select an area that's 12 rows by 16 columns. In the upper
left cell of the selection, type the formula =TRANSPOSE(Sheet1!$C$23:$N$38)
and press CTRL+SHIFT+ENTER.

BTW, you'll have to redo the formula if the size of the range changes (you add
or delete rows or columns).
 
J

John Mansfield

As an example assume you have this data with "M1" in cell A2 and "Jan" in
cell B1. The total area should be A1:E5:

Jan Feb Mar Apr
M1 1 5 9 13
M2 2 6 10 14
M3 3 7 11 15
M4 4 8 12 16

Go to the formula bar. Select the area A8:E11. Go to the formula bar and
type in the formula:

=TRANSPOSE(B1:E5)

Hit the CONTROL - ALT - ENTER keys at the same time.

The table will look like this:

Jan 1 2 3 4
Feb 5 6 7 8
Mar 9 10 11 12
Apr 13 14 15 16

Go to the formula bar. Select the area B7:E7. Go to the formula bar and
type in the formula:

=TRANSPOSE(A2:A5)

Hit the CONTROL - ALT - ENTER keys at the same time.

The finished table looks like this:

M1 M2 M3 M4
Jan 1 2 3 4
Feb 5 6 7 8
Mar 9 10 11 12
Apr 13 14 15 16
 
M

Myrna Larson

Typo there. It's CTRL+SHIFT+ENTER.

As an example assume you have this data with "M1" in cell A2 and "Jan" in
cell B1. The total area should be A1:E5:

Jan Feb Mar Apr
M1 1 5 9 13
M2 2 6 10 14
M3 3 7 11 15
M4 4 8 12 16

Go to the formula bar. Select the area A8:E11. Go to the formula bar and
type in the formula:

=TRANSPOSE(B1:E5)

Hit the CONTROL - ALT - ENTER keys at the same time.

The table will look like this:

Jan 1 2 3 4
Feb 5 6 7 8
Mar 9 10 11 12
Apr 13 14 15 16

Go to the formula bar. Select the area B7:E7. Go to the formula bar and
type in the formula:

=TRANSPOSE(A2:A5)

Hit the CONTROL - ALT - ENTER keys at the same time.

The finished table looks like this:

M1 M2 M3 M4
Jan 1 2 3 4
Feb 5 6 7 8
Mar 9 10 11 12
Apr 13 14 15 16
Click to expand...
 
Top