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

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

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

--
Don Guillett
SalesAid Software
[email protected]
"- Bobb -" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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.

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



"Dave" <[email protected]> wrote in message
news:[email protected]...
> 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

Did you try my suggestion?

--
Don Guillett
SalesAid Software
[email protected]
"- Bobb -" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
> "Dave" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

I did get the solution:
http://www.excelforum.com/showthread...4ccb4&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
================

"- Bobb -" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
> "Dave" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

Glad to help

--
Don Guillett
SalesAid Software
[email protected]
"- Bobb -" <[email protected]> wrote in message
news:[email protected]...
> I did get the solution:
>
http://www.excelforum.com/showthread...4ccb4&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
> ================
>
> "- Bobb -" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
> > "Dave" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
>
>