Can not Transpose with out destroying my formulas

[Jay]

I would like to know how to transpose the layout of a speadsheet from rows to columns with out destroyin my spread sheet...

When ever I do this I lose the equations I have entered.. for some reason the refereces tdo not folllow the trransposition...

HELP

 

[Max]

Assuming your source range is in A1:A26 (ie a 26R x 1C grid)

Try this:

Select say B1:AA1 (target range)
(note: the target range selected must be the "converse"
of the source 26R x 1C grid, i.e. a 1R x 26C grid)

Put in the formula bar: =TRANSPOSE(A1:A26)

Array-enter the formula, viz.:
Hold down CTRL + SHIFT, press ENTER
(instead of just pressing ENTER)

Done correctly Excel will wrap curly braces around the formula:

{=TRANSPOSE(A1:A26)}

(don't type-in the curly braces!)

Whatever's showing in A1:A26 will be transposed into B1:AA1

If A1:A26 contains formulas with values which may change,
then the changes will be correspondingly reflected in B1:AA1

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------

I would like to know how to transpose the layout of a speadsheet from rows

to columns with out destroyin my spread sheet....

When ever I do this I lose the equations I have entered.. for some reason

the refereces tdo not folllow the trransposition....

 

[Max]

Just to add-on a little..

If the source range is say
in Sheet1, in A1:A26 (as mentioned earlier)

you can also transpose to another sheet, say, Sheet2

In Sheet2:

Select say, A1:Z1 as the target range
(a 1R x 26C grid)

Put in the formula bar: =TRANSPOSE(Sheet1!A1:A26)

Array-enter the formula, viz.:
Hold down CTRL + SHIFT, press ENTER
(instead of just pressing ENTER)
--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
------------------------------------------

 

[J]

This did not work. all of the formulas were altered and the new sheet has the same format at the old... Suggestions...

 

[Dave Peterson]

You want the formulas to not adjust?

If you make your references absolute: =A1 becomes =$a$1 would that help.

If you don't want to adjust the references, then try selecting your range to
copy and

Edit|replace
=
with
$$$$$=
(now those formulas are simple text)

Copy|transpose

Select both the original range and the transposed range and
edit|Replace
$$$$$=
with
=

And excel will see them as formulas once again.

 

[J]

i do want my formulas to work!!! They do not when the sheet is transposed.. The spreadsheet represents financial projectction for 8 years. Each sheet has months for column, the forumlas that do the calculations reference varialble and some inputs on other sheets... ( need to change the format such that months are in the row

I will try this although I am not sure what I am doing. Other suggestions

 

[Max]

Perhaps you could describe some details of your present layout,
including typical formulae, and what exactly you're trying to do?

rgds,
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------

This did not work. all of the formulas were altered and the new sheet has

the same format at the old... Suggestions...

 

[Dave Peterson]

Try it and then post back with your results.

i do want my formulas to work!!! They do not when the sheet is transposed.. The spreadsheet represents financial projectction for 8 years. Each sheet has months for column, the forumlas that do the calculations reference varialble and some inputs on other sheets... ( need to change the format such that months are in the rows

I will try this although I am not sure what I am doing. Other suggestions?