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

[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

Instead it does this.
=+Sheet4!B3
=+Sheet4!B4
=+Sheet4!B5
=+Sheet4!B6

 

[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

 

[Ken Johnson]

Maybe...

=+INDIRECT(ADDRESS(3,ROWS($1:2),4,1,"Sheet4"))

Ken Johnson

 

[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

 

[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