Converting columns into rows?

  • Thread starter Thread starter Allen
  • Start date Start date
A

Allen

Hi,

I need to create many rows of data where each cell in a
row references a cell in a column, ie cells X1 Y1 Z1
reference cells A1 A2 A3.

Is there a way drag the formula in X1, =$A1, horizontally
across the row so the numbered row reference increases??
 
Allen,

You can do it, but only through the use of macros. The easiest way
(and the
only way for complicated formulas, though yours are simple) is:

Create your formulas in the normal manner, but copy DOWN the COLUMN.
Then
select the formulas and run this macro to convert the formulas to
strings:

Sub FormulaToText()
For Each myCell In Selection
myCell.Formula = "'" & myCell.Formula
Next myCell
End Sub

Then copy the strings and use Paste special transpose to get them into
row form, and then run this macro to convert them back to formulas:

Sub TransformToFormula()
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
End Sub

Set up two custom commandbar buttons to do these, and it is really
quite
simple. You can do a large block of cells at a time, so you needn't do
it row by row.

Also, if you do this sort of thing frequently, I have written an
add-in that will allow you to paste transposed links. I will send it
to you if you contact me privately.

HTH,
Bernie
MS Excel MVP
 
Hi Allen,

one possible solution is the Function TRANSPOSE
1. Highlight cells X1 - Z1
2. Enter =TRANSPOSE(A1:A3)
3. Enter this formula as array formula (CTRL+SHIFT+ENTER)

Another solution could be INDEX
In X1 enter
=INDEX($A1:$A999,COLUMN()-23)
and copy this formula

Frank
 
Back
Top