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

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

Advertisements

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
 
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
 
Ad

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top