Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transposing cells with formulas
I have an array with each cell containing formulas, and I want to transpose
it such that all the formulas in the cells stay the same and original and transposed arrays are linked to each other. I need the answer really badly Thank You |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transposing cells with formulas
Copy your range.
Right click where you want to paste <Paste Special... Check "Transpose" -- Regards, Dave "Kincal" wrote: I have an array with each cell containing formulas, and I want to transpose it such that all the formulas in the cells stay the same and original and transposed arrays are linked to each other. I need the answer really badly Thank You |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transposing cells with formulas
Have you actually tested this method?
Gord Dibben MS Excel MVP On Thu, 31 Aug 2006 11:37:02 -0700, David Billigmeier wrote: Copy your range. Right click where you want to paste <Paste Special... Check "Transpose" |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transposing cells with formulas
I use this macro I got from someone in one of the Excel groups.
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 Thu, 31 Aug 2006 11:33:01 -0700, Kincal wrote: I have an array with each cell containing formulas, and I want to transpose it such that all the formulas in the cells stay the same and original and transposed arrays are linked to each other. I need the answer really badly Thank You |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transposing cells with formulas
Yes, Excel transposes cell ranges within formula's as well (if you don't know
this I don't know why you are an MVP). The question you SHOULD have asked is "Did you mis-understand the post." I believe I did mis-understand by reading through your second post. -- Regards, Dave "Gord Dibben" wrote: Have you actually tested this method? Gord Dibben MS Excel MVP On Thu, 31 Aug 2006 11:37:02 -0700, David Billigmeier wrote: Copy your range. Right click where you want to paste <Paste Special... Check "Transpose" |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transposing cells with formulas
Not sure what you mean by this David.
"Excel transposes cell ranges within formulas" and how it applies to the subject. If I have a column of formulas say in column C like =A1+B1 down to =A20+B20 If I paste specialtranspose to D1 I get #REF! in D1 and across. Maybe I misunderstand your post? BTW........I didn't get to be an MVP because I was bright, just doggedly persistent. Gord On Thu, 31 Aug 2006 12:47:01 -0700, David Billigmeier wrote: Yes, Excel transposes cell ranges within formula's as well (if you don't know this I don't know why you are an MVP). The question you SHOULD have asked is "Did you mis-understand the post." I believe I did mis-understand by reading through your second post. Gord Dibben MS Excel MVP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transposing cells with formulas
Hi Gord -
I believe it was myself who originally mis-understood the subject's post. I must have read over the part when he said he wanted the formula's to stay the exact same. With respect to your example, however, select the entire range (A1:C20). Then go to a new cell (say D1) and do a Paste Special-Transpose. Excel will not only transpose the raw data but it will update each of the formula's to work with the new range. For example the first formula in the previous range was located in C1 and read "=A1+B1". After the transpose this will be located in D3 and read "=D1+D2." This is what I mean by "excel transposes cell ranges within formula's". Sorry for any confusion. -- Regards, Dave "Gord Dibben" wrote: Not sure what you mean by this David. "Excel transposes cell ranges within formulas" and how it applies to the subject. If I have a column of formulas say in column C like =A1+B1 down to =A20+B20 If I paste specialtranspose to D1 I get #REF! in D1 and across. Maybe I misunderstand your post? BTW........I didn't get to be an MVP because I was bright, just doggedly persistent. Gord On Thu, 31 Aug 2006 12:47:01 -0700, David Billigmeier wrote: Yes, Excel transposes cell ranges within formula's as well (if you don't know this I don't know why you are an MVP). The question you SHOULD have asked is "Did you mis-understand the post." I believe I did mis-understand by reading through your second post. Gord Dibben MS Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transposing cells with formulas
Now I see what you meant by the "excel transposes cell ranges within formulas"
You are correct if you also transpose the data the formulas are referencing. But OP states all the formulas in the cells stay the same and original and transposed arrays are linked to each other so maybe does want the entire array including formulas transposed? I duuno<g Gord On Thu, 31 Aug 2006 14:20:02 -0700, David Billigmeier wrote: Hi Gord - I believe it was myself who originally mis-understood the subject's post. I must have read over the part when he said he wanted the formula's to stay the exact same. With respect to your example, however, select the entire range (A1:C20). Then go to a new cell (say D1) and do a Paste Special-Transpose. Excel will not only transpose the raw data but it will update each of the formula's to work with the new range. For example the first formula in the previous range was located in C1 and read "=A1+B1". After the transpose this will be located in D3 and read "=D1+D2." This is what I mean by "excel transposes cell ranges within formula's". Sorry for any confusion. Gord Dibben MS Excel MVP |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transposing cells with formulas
Maybe he means something like this:
Original formulas in A1 to A25. Transpose from B1 to Z1, *And* arrays are *linked*. Enter in B1, and copy across to Z1: =INDEX($A$1:$A$25,COLUMNS($A:A)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Now I see what you meant by the "excel transposes cell ranges within formulas" You are correct if you also transpose the data the formulas are referencing. But OP states all the formulas in the cells stay the same and original and transposed arrays are linked to each other so maybe does want the entire array including formulas transposed? I duuno<g Gord On Thu, 31 Aug 2006 14:20:02 -0700, David Billigmeier wrote: Hi Gord - I believe it was myself who originally mis-understood the subject's post. I must have read over the part when he said he wanted the formula's to stay the exact same. With respect to your example, however, select the entire range (A1:C20). Then go to a new cell (say D1) and do a Paste Special-Transpose. Excel will not only transpose the raw data but it will update each of the formula's to work with the new range. For example the first formula in the previous range was located in C1 and read "=A1+B1". After the transpose this will be located in D3 and read "=D1+D2." This is what I mean by "excel transposes cell ranges within formula's". Sorry for any confusion. Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I skip blank cells when writing formulas | Excel Worksheet Functions | |||
Printing cells with conditional formats & formulas | Excel Discussion (Misc queries) | |||
Copying Formulas In Cells That Are Not Nested | Excel Discussion (Misc queries) | |||
Copying formulas to other cells. Keeping references w/o $ sign. | Excel Discussion (Misc queries) | |||
how do i protect cells in a shared worksheet | Excel Discussion (Misc queries) |