Transposing an array of formulae in Excel 1

[DQR]

I have a large block of formulae in an Excel worksheet, and I need to create a transpose of that block in another worksheet. That is, the formulae need to be the same as in the original block, but the formula in the ith row and jth column of sheet X now needs to appear in the jth row and the ith column of sheet Y.

I can do this with Paste Special | Transpose if all the formulae in the original block are absolute references (=$A$1, =$C$23 etc), but they aren’t, and there are far too many of them to change them all manually.

As a small example, suppose I have a 2x3 block of cells that contain formulae that deliver the result:

1 2 3
4 5 6

On another sheet, I need a 3x2 block of cells that delivers the transpose of those values:

1 4
2 5
3 6

But these cells need to change whenever the original cells change, so a simple transpose of the values won't do. There has to be a way - can anyone help me with this?

 

[GlennUK]

Select the 3x2 block that you want to have the formulae in, and type this:

=TRANSPOSE(Sheet1!A1:C2)

and press Ctrl-Shift-Enter, to create an array formula.

This assumes that you are getting the figures from a sheet called Sheet1, and that the range you want is A1 to C2, so alter these as necessary.

Good luck.

Cheers, Glenn.

 

[DQR]

Perfect - it worked like a dream. Many thanks!

 

[RichardMoran]

Nice one Glenn!

I did see this which may help people trying to convert a range of cells to use absolute references:-

http://support.microsoft.com/?kbid=213465

Rik

 

[KenWright]

If you don't want to keep the original set of formulas and only want to move but transpose them, then select the range to move, do edit / Replace, replacing = with %%. Nopw copy using paste special and transpose, and then just do edit / replace, replacing %% with =

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[dboddington]

I have visited this page quite a few time already, thanks GlenUK (a star from me)

 

[GlennUK]

My pleaseure

Cheers, Glenn.