copy formula as series

[Quercus]

I enter data in a column e.g. A1 : A50
How do I then in another sheet , by using the cell references, enter the
data as a row in sheet 2 without having to do each formula manually?

In sheet 2 when I try to copy the formula =sheet1!A1 across the row it
obviously puts in the relative value. I have tried fixing the column ref by
using =sheet1!$A1. That correctly fixes the clumn as A across the series but
fails to increment the row reference down the column. That is fixed as 1
where as I want it as a series A1: A50 in the row in sheet 2.

Help

 

[Bernie Deitrick]

Quercus,

Send me a message privately, and I will send you an add-in (written by me)
that will add a "Transpose Paste-Link" option to your cell right-click menu.

But, if you only want to do this occasionally, then you can copy your link
formula down the column, select all the cells with links, then run this
macro to convert the formulas to text:

Sub MakeText()
For Each myCell In Selection
myCell.Value = "'" & myCell.Value
Next myCell
End Sub

Then copy those cells, and paste special transpose at your destination for
the links. With your destinations cells selected, run this macro:

Sub TransformToFormula2()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
End Sub

And you're done.

HTH,
Bernie
MS Excel MVP

 

[Max]

One way ..

In Sheet2
-----------
Put in the starting cell, say, in B1:
=OFFSET(Sheet1!$A$1,COLUMN(A1)-1,)
Copy B1 across 50 columns to AY1

The above will dynamically "transpose"
what's in A1:A50 in Sheet1
into B1:AY1 in Sheet2

 

[Bernie Deitrick]

Quercus,

Also, you can select your 50 cells in a row on sheet2, then use the array
formula (entered with Ctrl-Shift-Enter)

=TRANSPOSE(Sheet1!A1:A50)

However, you won't be able to insert or delete individual cells/columns
within that range after you do this....

HTH,
Bernie
MS Excel MVP

 

[Gord Dibben]

Quercus

In A1 of Sheet2 enter this =INDIRECT("Sheet1!A"&(ROW()+(COLUMN()-1)*1))

Drag/copy across row 1 for 50 columns.

Gord Dibben Excel MVP