Referencing rows to columns

[kikim225]

I would like to know how to make a function that is copied along rows
by clicking the box until the + sign shows and dragging downward to
reference columns rather than rows.

For example, when copying a formula along the y axis (via the above
"click and drag" method so often used by all of us), the formula
naturally makes changes such that a reference to A4 becomes A5, A6,
A7, A8, ... etc. The reference changes along the y-axis as the
formula is copied vertically.

However, I need the formula to change from A4 to B4, C4, D4, E4, ..
etc as I copy the formula vertically using this same "click and drag"
method. Essentially, I'm asking about setting the row constant while
allowing only the columns to change AS I'M TRYING TO COPY FORMULAS
VERTICALLY. Can the direction of the "click & drag" copy function be
set to cross-reference (i.e. changing along the columns as I'm copying
along the rows)? Otherwise, I would have to go into each cell after
copying and change all the column values.

 

[Pete_UK]

Have a look in Excel Help at the OFFSET or INDIRECT functions - both
would allow you to do this.

Hope this helps.

Pete

 

[Tyro]

The row remains constant when you copy the formula across the row with click
and drag. A4 becomes B4 which becomes C4, etc.
Can you show us your formula in a cell like A1 and show us what you want
the copy of it to be in cell A2?

 

[RagDyer]

Since you didn't give any examples of your formulas, this will do as you
asked for the individual cells that you did mention.

Enter this in any cell and drag down to copy, and this will copy Row 4
*down* a column:

=INDEX($4:$4,ROWS($1:1))

If you need help adapting this to your formulas, post back.