Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transpoe with formulas
how do i transpose data while keeping linked or calculated data?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |