How can you make Excel to advance one row instead of one column?


For example you in A1 you have:
:=D1
One after draggin it one cell below (A2)i would like to have
:=E1
instead of
:=D2


any clue??

I don't believe copying the way you want is possible with built-in Excel
tools. I've had the same need as you though so I wrote this macro several
years ago and it has met my infrequent needs for what I call "transpose
copying". I don't even remember which situations it will work in and which
it will fail in, beyond simple ones like your example. So for what it's
worth...

This is the way you'd use it:
-In A1 enter =D1
-Select the range A1:A4
-Run macro.
-Formulas referencing E1, F1 and G1 should appear.

You can also select a horizontal range and get the fill going that way
referencing a vertical range.


''Fills a single cell across/down a selection transposing row/column
Sub FillTranspose()
Dim HoldBuffer As String, Counter As Long
Dim SrcCols As Long, SrcRows As Long
Dim KeyCell As Range, CalcMode As Long
Application.ScreenUpdating = False
CalcMode = Application.Calculation
Application.Calculation = xlManual
Set KeyCell = Selection.Cells(1)
SrcCols = Selection.Columns.Count
SrcRows = Selection.Rows.Count
If SrcRows = 1 And SrcCols 1 Then
For Counter = 2 To SrcCols
HoldBuffer = KeyCell.Offset(Counter - 1, 0).Formula
KeyCell.Copy
KeyCell.Offset(Counter - 1, 0).PasteSpecial xlFormulas
KeyCell.Offset(0, Counter - 1).Formula =
KeyCell.Offset(Counter - 1, 0).Formula
KeyCell.Offset(Counter - 1, 0).Formula = HoldBuffer
Next
ElseIf SrcCols = 1 And SrcRows 1 Then
For Counter = 2 To SrcRows
HoldBuffer = KeyCell.Offset(0, Counter - 1).Formula
KeyCell.Copy
KeyCell.Offset(0, Counter - 1).PasteSpecial xlFormulas
KeyCell.Offset(Counter - 1, 0).Formula = KeyCell.Offset(0,
Counter - 1).Formula
KeyCell.Offset(0, Counter - 1).Formula = HoldBuffer
Next
Else
MsgBox "Can only fill one row or one column"
End If
KeyCell.Select
Application.Calculation = CalcMode
End Sub


--
Jim Rech
Excel MVP
"Pawel Galecki" wrote in message
...
| How can you make Excel to advance one row instead of one column?
|
|
| For example you in A1 you have:
| :=D1
| One after draggin it one cell below (A2)i would like to have
| :=E1
| instead of
| :=D2
|
|
| any clue??

Enter this in A1, and drag down to copy as needed:

=OFFSET($D$1,,ROW(A1)-1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Pawe³ Ga³ecki" wrote in message
...
How can you make Excel to advance one row instead of one column?


For example you in A1 you have:
:=D1
One after draggin it one cell below (A2)i would like to have
:=E1
instead of
:=D2


any clue??