HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE

[Guest]

My cell c1 contains the formula b1-a1.when i copy this formula to cells d1
and e1 the cells d1 and e1 have the following formula :
d1=c1-b1
e1=d1-c1

but i want the following
d1 should be b2-a2 and
e2 should be b3-a3

how do i do this?

 

[Aladin Akyurek]

One way...

In C1 enter & copy across:

=INDEX($A:$A,COLUMN()-COLUMN($C$1)+1)-INDEX($B:$B,COLUMN()-COLUMN($C$1)+1)

 

[Ken Wright]

Copy C1 and paste it to C2:C100 or however many you need.
Select C2:C100 and do edit / Replace, replacing = with [
Select C2:C100 and copy
Select D1 and do Edit / Paste Special / Tranpose
Select D1 across to the last entry and do Edit / Replace, replacing [ with =
Delete what was in C2:C100

 

[Max]

Another way ..

Put in C1:

=OFFSET($B$1,COLUMNS($A$1:A1)-1,)-OFFSET($B$1,COLUMNS($A$1:A1)-1,-1)

Copy C1 across to E1

C1 to E1 will return :

B1-A1
B2-A2
B3-A3
etc

 

[Max]

Think Aladin meant in C1, copied across:

=INDEX($B:$B,COLUMN()-COLUMN($C$1)+1)-INDEX($A:$A,COLUMN()-COLUMN($C$1)+1)

(the other way around <g>)