transpoe with formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dragomira - 02 May 2008 16:31 GMT

how do i transpose data while keeping linked or calculated data?

Reply to this Message

ryguy7272 - 02 May 2008 17:20 GMT

Select the range > Edit > Copy > Select Destination > Edit > Paste Special >
Transpose > OK

Regards,
Ryan---

Signature

RyGuy

> how do i transpose data while keeping linked or calculated data?

Reply to this Message

Bernie Deitrick - 02 May 2008 17:29 GMT

Depends on what you mean by that - do you want links to the data, with the links transposing the
data, or keeping the existing formulas but transposing them?

Select the cells, run the first macro, copy, paste special transpose where you want, then run the
second macro with the copied cells selected.

HTH,
Bernie
MS Excel MVP

Sub SAFormulaToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myCell.Formula = "'" & myCell.Formula
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub

Sub SATextToFormula()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub

> how do i transpose data while keeping linked or calculated data?

Reply to this Message

dragomira - 02 May 2008 21:04 GMT

i want to keep the existing links but transpose them. how do i do that? what
do you mean by the two macros? i havent really done this before, so any
details would be helpful.
thanks so much

> Depends on what you mean by that - do you want links to the data, with the links transposing the
> data, or keeping the existing formulas but transposing them?
[quoted text clipped - 55 lines]
>
> > how do i transpose data while keeping linked or calculated data?

Reply to this Message

Gord Dibben - 02 May 2008 22:33 GMT

Assuming linking formulas are in A1:A20

Select B1 and enter this formula =INDEX($A:$A,(ROWS($1:1)-1)*1+COLUMNS($A:B)-1)

Drag/copy across 19 columns.

Or this one =INDIRECT("Sheet2!A"&COLUMN(A1))

Gord Dibben MS Excel MVP

>i want to keep the existing links but transpose them. how do i do that? what
>do you mean by the two macros? i havent really done this before, so any
[quoted text clipped - 60 lines]
>>
>> > how do i transpose data while keeping linked or calculated data?

Reply to this Message