transpoe with formulas
 

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

transpoe with formulas
 

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


Regards,
Ryan---


--
RyGuy


"dragomira" wrote:

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

transpoe with formulas
 

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



"dragomira" wrote in message
...
how do i transpose data while keeping linked or calculated data?

transpoe with formulas
 

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
"Bernie Deitrick" wrote:

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



"dragomira" wrote in message
...
how do i transpose data while keeping linked or calculated data?

transpoe with formulas
 

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


On Fri, 2 May 2008 13:04:02 -0700, dragomira
wrote:

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
"Bernie Deitrick" wrote:

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



"dragomira" wrote in message
...
how do i transpose data while keeping linked or calculated data?