Reference columns to rows

sample worksheet range is from a1 to g6

assume your data is in column a, and all columns to the right of it are blank.

in cell b2, input the nubmer 1. in cell c2, input the formula =+B2+1 (the result should be 2). copy your formula to g2.

next step. go to cell b1, input formula ="=a"&""&B2 (the result should be =a1). copy this formula to cell g1.

next step. copy range b1 to g1 and paste as values.

next step. highlight (in this case, go to cell b1) cell b1, go to Data-> Text to Columns-> choose "General" formatting. Repeat this step through g1.

at this point, cell b1 should have the formula =a1 with the results.

hope this works

cheers
- tom

run this macro if you want to see how i did it.

Sub MacroStepByStep()

Application.Goto Reference:="R1C1"
ActiveCell.FormulaR1C1 = "kdfadf"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "dfjdljf"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "a"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "fsdljf"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "akflaj"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "fksalkf"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R2C2"
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=+RC[-1]+1"
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Application.Goto Reference:="R1C2"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=""=a""&""""&R[1]C"
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Application.Goto Reference:="R1C2"
ActiveCell.Range("A1:F1").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="R1C2"
Application.CutCopyMode = False
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
Application.Goto Reference:="R1C1"
End Sub

Augie
Let's say your data is in cells A1:A10 on Sheet1.
In Sheet2 select cells A1:J1, type in the following formula and press Ctrl+Shift+Enter :-
=TRANSPOSE(Sheet1!A1:A10)
Celia