I have a 2007 worksheet that has formulas in a Column:
=Tally!D10
=Tally!D11
=Tally!D12

How do I transpose the formula columns to rows like:
==Tally!D10 =Tally!D11 =Tally!D12

Now when I copy and Paste special (Transpose) is translates like:
==Tally!D10 =Tally!E10 =Tally!F10

Which is not the data I need?

Copy Paste Special Values Transpose

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Terri" wrote:

I have a 2007 worksheet that has formulas in a Column:
=Tally!D10
=Tally!D11
=Tally!D12

How do I transpose the formula columns to rows like:
==Tally!D10 =Tally!D11 =Tally!D12

Now when I copy and Paste special (Transpose) is translates like:
==Tally!D10 =Tally!E10 =Tally!F10

Which is not the data I need?

Let's assume you want the formulas in A1:C1

Enter this formula in A1 and copy across to C1:

=INDEX(Tally!$D10:$D12,COLUMNS($A1:A1))

--
Biff
Microsoft Excel MVP


"Terri" wrote in message
...
I have a 2007 worksheet that has formulas in a Column:
=Tally!D10
=Tally!D11
=Tally!D12

How do I transpose the formula columns to rows like:
==Tally!D10 =Tally!D11 =Tally!D12

Now when I copy and Paste special (Transpose) is translates like:
==Tally!D10 =Tally!E10 =Tally!F10

Which is not the data I need?

You can copy then paste specialtranspose but your cell references must
first be absolute.

=Tally!$D$10
=Tally!$D$11
=Tally!$D$12

Then you copy and transpose.

=Tally!$D$10 =Tally!$D$11 =Tally!$D$12

Or experiment with INDIRECT function.

Assuming your original formulas are in A1:A3

=INDIRECT("A"&COLUMN(A1)) entered in B1 and copied across will do same thing
as copy and transpose.


Gord Dibben MS Excel MVP

On Wed, 2 Dec 2009 14:05:01 -0800, Terri
wrote:

I have a 2007 worksheet that has formulas in a Column:
=Tally!D10
=Tally!D11
=Tally!D12

How do I transpose the formula columns to rows like:
==Tally!D10 =Tally!D11 =Tally!D12

Now when I copy and Paste special (Transpose) is translates like:
==Tally!D10 =Tally!E10 =Tally!F10

Which is not the data I need?

This works, however. I have 97 rows and 411 columns with a formula i.e.
=Tally!D10 in them. How can I convert them all to adbsolute without manually
entering the $ symbols in each cell i.e.=Tally!$D$10?

"Gord Dibben" wrote:

You can copy then paste specialtranspose but your cell references must
first be absolute.

=Tally!$D$10
=Tally!$D$11
=Tally!$D$12

Then you copy and transpose.

=Tally!$D$10 =Tally!$D$11 =Tally!$D$12

Or experiment with INDIRECT function.

Assuming your original formulas are in A1:A3

=INDIRECT("A"&COLUMN(A1)) entered in B1 and copied across will do same thing
as copy and transpose.


Gord Dibben MS Excel MVP

On Wed, 2 Dec 2009 14:05:01 -0800, Terri
wrote:

I have a 2007 worksheet that has formulas in a Column:
=Tally!D10
=Tally!D11
=Tally!D12

How do I transpose the formula columns to rows like:
==Tally!D10 =Tally!D11 =Tally!D12

Now when I copy and Paste special (Transpose) is translates like:
==Tally!D10 =Tally!E10 =Tally!F10

Which is not the data I need?




.

Run this macro after selecting all cells with the relative references.

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


Gord

On Fri, 4 Dec 2009 06:55:01 -0800, Terri
wrote:

This works, however. I have 97 rows and 411 columns with a formula i.e.
=Tally!D10 in them. How can I convert them all to adbsolute without manually
entering the $ symbols in each cell i.e.=Tally!$D$10?

"Gord Dibben" wrote:

You can copy then paste specialtranspose but your cell references must
first be absolute.

=Tally!$D$10
=Tally!$D$11
=Tally!$D$12

Then you copy and transpose.

=Tally!$D$10 =Tally!$D$11 =Tally!$D$12

Or experiment with INDIRECT function.

Assuming your original formulas are in A1:A3

=INDIRECT("A"&COLUMN(A1)) entered in B1 and copied across will do same thing
as copy and transpose.


Gord Dibben MS Excel MVP

On Wed, 2 Dec 2009 14:05:01 -0800, Terri
wrote:

I have a 2007 worksheet that has formulas in a Column:
=Tally!D10
=Tally!D11
=Tally!D12

How do I transpose the formula columns to rows like:
==Tally!D10 =Tally!D11 =Tally!D12

Now when I copy and Paste special (Transpose) is translates like:
==Tally!D10 =Tally!E10 =Tally!F10

Which is not the data I need?




.