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" <anantth@discussions.microsoft.com> wrote in message
news:5D3D2C9A-86A0-4BE3-9C96-148594429575@microsoft.com...
> 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 <at>yahoo<dot>com
----
anantth <anantth@discussions.microsoft.com> wrote in message
news:5D3D2C9A-86A0-4BE3-9C96-148594429575@microsoft.com...
> 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 <at>yahoo<dot>com
----
Aladin Akyurek <akyurek@xs4all.nl> wrote in message
news:4205DFE8.9050703@xs4all.nl...
> 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?