I have a fairly large (6MB) and mature spreadsheet that I need to have the
columns and rows reversed? (ie. names in the left column and measurements in
the top row) Is there a global function or is this one large pivot table?
--
Thank you,
Chris H

If its a one time job, try a copy paste special transpose ok over to a
new sheet.

If it's to be dynamic to the source, one way is via using TRANSPOSE()

Assume source range is in Sheet1, in A1:T5 (a 5R x 20C range)

In Sheet2,

Select A1:E20 (a 20R x 5C converse range)

Put in the formula bar: =TRANSPOSE(Sheet1!A1:T5)
Array-enter the formula by pressing CTRL+SHIFT+ENTER

A1:E20 returns a dynamic transpose of what's in A1:T5 in Sheet1

For a neater look, we could suppress extraneous zeros display via: Tools
Options View tab Uncheck "Zero values" OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"ChrisH" wrote in message
...
I have a fairly large (6MB) and mature spreadsheet that I need to have the
columns and rows reversed? (ie. names in the left column and measurements
in
the top row) Is there a global function or is this one large pivot table?
--
Thank you,
Chris H

I'm guessing that a 6MB file might have more than a 5R x 20C range.

<vbg

Cheers!

Biff

"Max" wrote in message
...
If its a one time job, try a copy paste special transpose ok over to
a
new sheet.

If it's to be dynamic to the source, one way is via using TRANSPOSE()

Assume source range is in Sheet1, in A1:T5 (a 5R x 20C range)

In Sheet2,

Select A1:E20 (a 20R x 5C converse range)

Put in the formula bar: =TRANSPOSE(Sheet1!A1:T5)
Array-enter the formula by pressing CTRL+SHIFT+ENTER

A1:E20 returns a dynamic transpose of what's in A1:T5 in Sheet1

For a neater look, we could suppress extraneous zeros display via: Tools
Options View tab Uncheck "Zero values" OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"ChrisH" wrote in message
...
I have a fairly large (6MB) and mature spreadsheet that I need to have
the
columns and rows reversed? (ie. names in the left column and measurements
in
the top row) Is there a global function or is this one large pivot table?
--
Thank you,
Chris H

"Biff" wrote:
I'm guessing that a 6MB file might have more
than a 5R x 20C range. <vbg

Most probably! <g

Purely for ease of set-up, especially for huge source ranges, I'd prefer
using the non-array OFFSET (rather than TRANSPOSE)

Assume source range is in Sheet1, in A1:IV50 (a 50R x 256C range)

In Sheet2, with A1 containing:
=OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1)

Just fill across & down (or down & across)
to cover the converse grid size (256R x 50C)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----