Fill rows with column information

[Guest]

When a reference to a cell is included in another cell and the user drags the
handle to fill adjacent cells Excel increments the column if the user drags
down the column or increments the row if the user drags across the row.
E.g. cell G2 has =A1
If the fill handle of G2 is dragged along column G then Excel increments the
formula to =A2, =A3, =A4 e.t.c.
If the fill handle of G2 is dragged along row 2 then Excel increments the
formula to =B1, =C1, =D1 e.t.c.
Is there a way to drag the fill handle along the row and have Excel
increment the column?
Or vice versa?

Tim

 

[Bob Umlas]

Yes. Right from my book "This Isn't Excel, it's Magic"
Change the formula in G2 from =A1 to A1 (no equal sign)
Now, the fill handle when dragged right will change it to A2, A3, A4, ....
When you're done, Select these, then use Edit/Replace, replacing A with =A
and you're done.
If you want to drag down and have the values go across, it's a bit trickier.
Instead of using =A1, change it to R1C1 (no "="). (if the formula were =E7,
you'd use R7C5).
Now, fill this down and you'll see R1C2, R1C3,... Before you can change this
to formulas, you need to use Tools/Options/General tab, select R1C1
reference Style. Then select the R1Cx cells, Edit/Replace R with =R. Then
revisit Tools/Options/General and deselect R1C1 reference style, and you're
done.
Bob Umlas
Excel MVP

 

[Ragdyer]

How about a couple of simple formulas:

Drag down a column to return the contents of a row, in this case, Row1:
=INDEX($1:$1,ROWS($1:1))

Drag along a row to return the contents of a column, in this case, Column A:
=INDEX($A:$A,COLUMNS($A:A))