transposing cells with formulas

[Guest]

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

 

[Guest]

Copy your range.
Right click where you want to paste
<Paste Special...>
Check "Transpose"

 

[Gord Dibben]

Have you actually tested this method?


Gord Dibben MS Excel MVP

 

[Gord Dibben]

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

 

[Guest]

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]

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 special>transpose 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

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

 

[Guest]

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]

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

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

 

[RagDyer]

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))