# Copy formula - how to keep row the same but incrament the column

C

#### Craig

I am trying to copy a formula but I want to keep the row the same and have
the column incrament by one.

Here is an example of what I would like excel to do when I copy the formula
to the cells below.
=+Sheet4!B3
=+Sheet4!C3
=+Sheet4!D3
=+Sheet4!E3

=+Sheet4!B3
=+Sheet4!B4
=+Sheet4!B5
=+Sheet4!B6

P

#### Pete_UK

You could do it this way:

=INDIRECT("Sheet4!"&CHAR(ROW(A1)+65)&"3")

This will start at column B and will work up to column Z - just copy
it down.

Hope this helps.

Pete

K

Maybe...

Ken Johnson

C

#### Craig

Hi Ken,

This works perfectly. Could you explain a little bit about the ROWS(\$1:2)
part of it. I know it refers to the column_num part of the ADDRESS command.
I just don't quite understand how it works.

Thanks,

Craig

K

#### Ken Johnson

Hi Craig,

ROWS(\$1:2) returns the number of rows in the range \$1:2 which is 2
(row 1 and row 2). The column is then the 2nd column or column B.
When the formula is filled down to the next row it changes to ROWS
(\$1:3) since the \$ freezes the 1 and the 2 without the \$ is
incremented to 3. Then ROWS(\$1:3) returns 3 (rows 1, 2 and 3) and the
column is then the 3rd column or column C. And so on down the column.

Hope this makes sense.

Ken Johnson