HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE
 

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?

One way...

In C1 enter & copy across:

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

anantth wrote:
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?

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"anantth" wrote in message
...
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?

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
anantth wrote in message
...
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?

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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Aladin Akyurek wrote in message
...
One way...

In C1 enter & copy across:

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

anantth wrote:
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?