Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any quick way for transposing formulas?
Dear Sir
I understand that I need to change the cell address in a formula from relative to absolute before I could transpose it. E.g. A B C 1 Code Date Amount 2 M236 xxx 250 3 K254 xxx 360 4 T841 xxx 550 5 K103 xxx 750 6 T145 xxx 320 From To =C2 =$C$2 =C3 =$C$3 =C4 =$C$4 =C5 =$C$5 =C6 =$C$6 Transposing =$C$2 =$C$3 =$C$4 =$C$5 =$C$6 My problem is if I copy a relative cell, I do not need to change the row or column numbers, but I need to enter the $ sign for each cell. If I choose to copy an absolute formula cell, I would need to enter the row or column numbers. May I know if there is a quick way for changing a formula from absolute to relative without having to enter $ sign or changing the row or column numbers manually ? Thanks Low -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any quick way for transposing formulas?
Hi,
Not sure if this will work in XL2002 but... Select your range and do a find and replace (CTRL+H) and in Find What enter =C and Replace with =$C$ Thsi works in XL2003 Hope this helps, Gav. "Mr. Low" wrote: Dear Sir I understand that I need to change the cell address in a formula from relative to absolute before I could transpose it. E.g. A B C 1 Code Date Amount 2 M236 xxx 250 3 K254 xxx 360 4 T841 xxx 550 5 K103 xxx 750 6 T145 xxx 320 From To =C2 =$C$2 =C3 =$C$3 =C4 =$C$4 =C5 =$C$5 =C6 =$C$6 Transposing =$C$2 =$C$3 =$C$4 =$C$5 =$C$6 My problem is if I copy a relative cell, I do not need to change the row or column numbers, but I need to enter the $ sign for each cell. If I choose to copy an absolute formula cell, I would need to enter the row or column numbers. May I know if there is a quick way for changing a formula from absolute to relative without having to enter $ sign or changing the row or column numbers manually ? Thanks Low -- A36B58K641 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any quick way for transposing formulas?
First enter Dibben's macro:
Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula _ (cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Then select the cells and run the macro. -- Gary''s Student - gsnu200777 "Mr. Low" wrote: Dear Sir I understand that I need to change the cell address in a formula from relative to absolute before I could transpose it. E.g. A B C 1 Code Date Amount 2 M236 xxx 250 3 K254 xxx 360 4 T841 xxx 550 5 K103 xxx 750 6 T145 xxx 320 From To =C2 =$C$2 =C3 =$C$3 =C4 =$C$4 =C5 =$C$5 =C6 =$C$6 Transposing =$C$2 =$C$3 =$C$4 =$C$5 =$C$6 My problem is if I copy a relative cell, I do not need to change the row or column numbers, but I need to enter the $ sign for each cell. If I choose to copy an absolute formula cell, I would need to enter the row or column numbers. May I know if there is a quick way for changing a formula from absolute to relative without having to enter $ sign or changing the row or column numbers manually ? Thanks Low -- A36B58K641 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any quick way for transposing formulas?
Or don't use that macro, rather use this one from I believe John
Walkenbach,,,,,,,,,maybe Tom Ogilvy. You pick the range to transpose and the range to paste to. Transposes without adding the Absolute $ signs. Sub Transpose_Formulas() Dim SRange As Range, dCell As Range Dim sCell As Range, i As Integer, J As Integer Dim str As String 'get input ranges. default box is filled by use of text 'variable set to the selected address str = Selection.Address(False, False) Application.ScreenUpdating = True On Error Resume Next Set SRange = Application.InputBox(prompt:= _ "Select the range of cells to be transposed." & Chr(10) & Chr(10) _ & "If cells do not have Formulas, Sub will end!.", _ Type:=8, Default:=str) If Not SRange.HasFormula Then MsgBox "Cells do not contain formulas" End Else If SRange.HasFormula Then Set dCell = Application.InputBox(prompt:= _ "Select the top left cell of the output location.", _ Type:=8) If dCell Is Nothing Then End On Error GoTo 0 'set single cell references for use in the next step Set sCell = SRange.Cells(1, 1) Set dCell = dCell.Cells(1, 1) 'loop through all cells, working backward to the top left cell For i = SRange.Rows.Count - 1 To 0 Step -1 For J = SRange.Columns.Count - 1 To 0 Step -1 If i 0 Or J 0 Then 'do this for all but the first cell sCell.Offset(i, J).Cut _ Destination:=dCell.Offset(J, i) Else 'do top corner last. Otherwise references are changed sCell.Cut Destination:=dCell End If Next J Next i End If End If End Sub Gord Dibben MS Excel MVP On Fri, 4 Apr 2008 07:49:01 -0700, Gary''s Student wrote: First enter Dibben's macro: Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula _ (cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Then select the cells and run the macro. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any quick way for transposing formulas?
Dear Sir,
Thanks for the code. Low -- A36B58K641 "Gary''s Student" wrote: First enter Dibben's macro: Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula _ (cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Then select the cells and run the macro. -- Gary''s Student - gsnu200777 "Mr. Low" wrote: Dear Sir I understand that I need to change the cell address in a formula from relative to absolute before I could transpose it. E.g. A B C 1 Code Date Amount 2 M236 xxx 250 3 K254 xxx 360 4 T841 xxx 550 5 K103 xxx 750 6 T145 xxx 320 From To =C2 =$C$2 =C3 =$C$3 =C4 =$C$4 =C5 =$C$5 =C6 =$C$6 Transposing =$C$2 =$C$3 =$C$4 =$C$5 =$C$6 My problem is if I copy a relative cell, I do not need to change the row or column numbers, but I need to enter the $ sign for each cell. If I choose to copy an absolute formula cell, I would need to enter the row or column numbers. May I know if there is a quick way for changing a formula from absolute to relative without having to enter $ sign or changing the row or column numbers manually ? Thanks Low -- A36B58K641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: Any quick way for editing VLOOKUP formulas ? | Excel Discussion (Misc queries) | |||
Excel 2002 Pivot Table: Can I use it for transposing data ? | Excel Discussion (Misc queries) | |||
Excel 2002: Any quick way of transposing data ? | Excel Discussion (Misc queries) | |||
Excel 2002 : Any Quick Sub Total Formula ? | Excel Discussion (Misc queries) | |||
quick books and excel 2002 | New Users to Excel |