Copying cell References from Columns to Rows

[mickn74]

Without manually having to enter every cell reference I need ot copy multiple
cells from a column into various rows

Eg : I want to copy
Comunm M : M162:M166 (M168:M172 ...............) etc
to Row 138 : C138, D138, E138, F138, G138
Row 139 : C,D,E,F,G
Row 140 etc

Is there a quick copy and paste method for this

Or do you have to refernce every cell

Regadrs,

Michael

Regadrs,

Mick

 

[Jarek Kujawa]

do you mean this?

select M162:M166
Edit->Copy (or CTRL+C)
go to C138
Edit->PasteSpecial->select Transpose

etc.

 

[mickn74]

Jarek,

Thanks a heap a new litlle trick, I had to use the Transpose and Values as
it copied the formulas in which gave an incorrect value as the formulas are
looking at different cells. Yet the clicking the Values as well fixed it up

Thanks

Mick

 

[mickn74]

Jarek

I realised once I posted my last response this didnt fix my problem as
Transpose will copy a formula or value into the cell and go across the row
yet the formula looks at different cell references, yet I need it to be
referencing the calculation in the cell as it will be changing all the time.

Eg Cell C138 (=M162), D138 (=M163), E138 (=M164) etc etc

Is there some fast way to make the cell $M$162 without having to manually
reference each cell

thanks

 

[Gord Dibben]

Select the cells and run this macro before transposing.

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 Dibben MS Excel MVP