Formula changing when I move the data to new column - how to prevent change

[- Bobb -]

Simplified version of my file is:

Col a b c d e

Portfolio 7/29/2005 / For the Week / YTD / 7/22/2005
abc corp 10,000 +500 8% 9500
def corp 10,000 - 200 4% 10200
mmm 10,000 +100 0% 9900
ppp corp 10,000 +322 -1.2% 9678
xyz corp 10,000 +12 1% 9988
Total 50,000 834 x% 49,266

I have an excel file as above.
Each week, I copy/insert b1-b20 to the Column E (so previous week slides
to the right) then I update the leftmost column with current info. My "For
the week " column is c2 = b2 - e2. I want it to stay so. BUT when I
insert col b values to col E, (so that col E now becomes col F)
the formula for C2 changes to c2=b2-F2 (following the data)
I want C2 to ALWAYS be c2 = b2 - e2. I want C3 to ALWAYS be c3 = b3 - e3.
(and same for other cels in the c column)
I never want the formula containing E2 to change to F2 on its own

How to "lock the formula" for those cells in col C ? I've tried all the HELP
in Excel/online. Can't find it.
Any guidance ?

Thanks very much.
Bobb

 

[Don Guillett]

try this
=B2-INDIRECT("e"&ROW())

 

[Dave]

Excel uses relative cell referencing because row/cell
additions/deletions shift cells around. So the formulas still work but
their references change. OK, so you knew that! Methinks what you need
to do is use absolute cell references. Here's an example:
$C$3=$B$3-$E$3. The dollar sign must be in front of both the column and
row address.

 

[- Bobb -]

Dave,

I've tried that - it doesn't work.
Although I did not enter $C$3 anywhere (assuming you included just as a
written explanation). Using my earier example, after copy/insert it changes
the formula for col C cells from $B$3-$E$3 to $B$3-$F$3....

Bobb

Excel uses relative cell referencing because row/cell
additions/deletions shift cells around. So the formulas still work but
their references change. OK, so you knew that! Methinks what you need
to do is use absolute cell references. Here's an example:
$C$3=$B$3-$E$3. The dollar sign must be in front of both the column and
row address.

Simplified version of my file is:

Col a b c d e

Portfolio 7/29/2005 / For the Week / YTD / 7/22/2005
abc corp 10,000 +500 8% 9500
def corp 10,000 - 200 4% 10200
mmm 10,000 +100 0% 9900
ppp corp 10,000 +322 -1.2% 9678
xyz corp 10,000 +12 1% 9988
Total 50,000 834 x% 49,266

I have an excel file as above.
Each week, I copy/insert b1-b20 to the Column E (so previous week slides
to the right) then I update the leftmost column with current info. My "For
the week " column is c2 = b2 - e2. I want it to stay so. BUT when I
insert col b values to col E, (so that col E now becomes col F)
the formula for C2 changes to c2=b2-F2 (following the data)
I want C2 to ALWAYS be c2 = b2 - e2. I want C3 to ALWAYS be c3 = b3 - e3.
(and same for other cels in the c column)
I never want the formula containing E2 to change to F2 on its own

How to "lock the formula" for those cells in col C ? I've tried all the HELP
in Excel/online. Can't find it.
Any guidance ?

Thanks very much.
Bobb

 

[- Bobb -]

I did get the solution:
http://www.excelforum.com/showthread.php?s=dd85087e2eecb8de3518f175e324ccb4&t=392556

Re: Formula changing when I move the data to new column - how to prevent
change

----------------------------------------------------------------------------
----

try this
=B2-INDIRECT("e"&ROW())

I tried that and it works just fine.

Bobb
================

 

[Don Guillett]

Did you try my suggestion?

 

[Don Guillett]

Glad to help