Can not Transpose with out destroying my formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
-------------------------------------------
Jay said:
I would like to know how to transpose the layout of a speadsheet from rows
Click to expand...
to columns with out destroyin my spread sheet....
When ever I do this I lose the equations I have entered.. for some reason
Click to expand...
the refereces tdo not folllow the trransposition....
 
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
------------------------------------------
 
This did not work. all of the formulas were altered and the new sheet has the same format at the old... Suggestions...
 
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.
 
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
 
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
--------------------------------------------------
J said:
This did not work. all of the formulas were altered and the new sheet has
Click to expand...
the same format at the old... Suggestions...
 
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?
Click to expand...
 
Back
Top