Autofill equations

[Eric]

I have a situation I've never run into. I have a simple equation like =C1-D1
located in cell A1.

I want to copy this equation from A1 through A100. I can use Autofill to do
it and it would update the cell reference from C2 to C3 to C4 etc.

But what I want is that as I copy the equation down, the column references
would update rather than the row references. So as I copy from A1 to A2, the
equation would then read =D1-E1.

Any help would be appreciated.

-Eric

 

[Luke M]

Try this:

=OFFSET($B$1,0,ROW(A1))-OFFSET($C$1,0,ROW(A1))

 

[Bob Umlas]

Put this in A1 instead of C1-D1:
=OFFSET($A$1,0,ROW()*2,1)-OFFSET($A$1,0,ROW()*2+1,1)
this IS C1-D1 but as you fill down, it does what you requested...

 

[Elkar]

Try this:

=INDIRECT(ADDRESS(1,ROW()+3))-INDIRECT(ADDRESS(1,ROW()+4))

HTH
Elkar

 

[Luke M]

OP is not skipping 2 columns, thus no need for the "*2" operators.