how do i overwrite a cell and keep adding to another figure?

[Paula_p]

Hi everyone,
I'm trying to create an inventory sheet where i have a correct record of
goods available for sale.

What i want is to be able to overwrite figures in a particular column and
have that added to figures in another column. For example. Say i have 25 Cds
and i sold 10, i now have 15 availible for sale. However, if i bought 50
more, i should now have 65. Is there a way i can write the figure bought in a
cell and have that added to the figure in the existing stock?

Also, each time stock for a particular product is bought it is recorded in
the same cell, ie. the old figure is overwritten, so the 25 is replaced with
50.
Thanks.

[Peo Sjoblom]

http://www.mcgimpsey.com/excel/accumulator.html

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Paula_p" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone,
> I'm trying to create an inventory sheet where i have a correct record of
> goods available for sale.
>
> What i want is to be able to overwrite figures in a particular column and
> have that added to figures in another column. For example. Say i have 25
> Cds
> and i sold 10, i now have 15 availible for sale. However, if i bought 50
> more, i should now have 65. Is there a way i can write the figure bought
> in a
> cell and have that added to the figure in the existing stock?
>
> Also, each time stock for a particular product is bought it is recorded in
> the same cell, ie. the old figure is overwritten, so the 25 is replaced
> with
> 50.
> Thanks.

[Paula_p]

I found the info. on the website very helpful. However, there is one problem
i cant seem to fix. Each time i enter the new figure it is added to the
existing figure i want, however if i should shift between worksheets or
change data in another cell which has no connection to the two columns im
using, the new figure keeps adding itself again and again. Example if i
overwrite 30 and my new figure is 70 if i edit other cells, the new figure
changes to 100, and if i do other changes it will change to 130 ans so on.
How do i correct this?

"Peo Sjoblom" wrote:

> http://www.mcgimpsey.com/excel/accumulator.html
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "Paula_p" <[email protected]> wrote in message
> news:[email protected]...
> > Hi everyone,
> > I'm trying to create an inventory sheet where i have a correct record of
> > goods available for sale.
> >
> > What i want is to be able to overwrite figures in a particular column and
> > have that added to figures in another column. For example. Say i have 25
> > Cds
> > and i sold 10, i now have 15 availible for sale. However, if i bought 50
> > more, i should now have 65. Is there a way i can write the figure bought
> > in a
> > cell and have that added to the figure in the existing stock?
> >
> > Also, each time stock for a particular product is bought it is recorded in
> > the same cell, ie. the old figure is overwritten, so the 25 is replaced
> > with
> > 50.
> > Thanks.
>
>
>

[Gord Dibben]

Paula

Which of JE's methods did you use? The VBA or the Circular Reference.?

Are you sure you want to do this?

Think about it after reading the following.

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in Tools>Options>Calculation check
Iterations and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in
the Application D4 will update even if no new number is entered in
C4. NOT GOOD.
Note 2. This operation is not recommended because you will have no
"paper trail" to follow. Any mistake in entering a new number in C4
cannot be corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4
and Edit>Enter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
which does what you want without the re-calculation problem, but
again there is no "paper trail" for back-checking in case of errors
in data input.

http://longre.free.fr/english/func_cats.htm


Gord Dibben Excel MVP




On Fri, 9 Jun 2006 13:15:02 -0700, Paula_p <[email protected]>
wrote:

>I found the info. on the website very helpful. However, there is one problem
>i cant seem to fix. Each time i enter the new figure it is added to the
>existing figure i want, however if i should shift between worksheets or
>change data in another cell which has no connection to the two columns im
>using, the new figure keeps adding itself again and again. Example if i
>overwrite 30 and my new figure is 70 if i edit other cells, the new figure
>changes to 100, and if i do other changes it will change to 130 ans so on.
>How do i correct this?
>
>"Peo Sjoblom" wrote:
>
>> http://www.mcgimpsey.com/excel/accumulator.html
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> Excel 95 - Excel 2007
>> Northwest Excel Solutions
>> www.nwexcelsolutions.com
>> "It is a good thing to follow the first law of holes;
>> if you are in one stop digging." Lord Healey
>>
>>
>> "Paula_p" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi everyone,
>> > I'm trying to create an inventory sheet where i have a correct record of
>> > goods available for sale.
>> >
>> > What i want is to be able to overwrite figures in a particular column and
>> > have that added to figures in another column. For example. Say i have 25
>> > Cds
>> > and i sold 10, i now have 15 availible for sale. However, if i bought 50
>> > more, i should now have 65. Is there a way i can write the figure bought
>> > in a
>> > cell and have that added to the figure in the existing stock?
>> >
>> > Also, each time stock for a particular product is bought it is recorded in
>> > the same cell, ie. the old figure is overwritten, so the 25 is replaced
>> > with
>> > 50.
>> > Thanks.
>>
>>
>>

Gord Dibben MS Excel MVP

[Paula_p]

I was using the circular refference. D4 does not update when i input a number.

"Gord Dibben" wrote:

> Paula
>
> Which of JE's methods did you use? The VBA or the Circular Reference.?
>
> Are you sure you want to do this?
>
> Think about it after reading the following.
>
> You can have a cumulative total in a cell if you have a
> separate source cell for adding a new total to the original.
>
> Use at your own risk. I am Posting this just to show you how it can
> be done, not as a good solution. You would be much better off to
> have another column so you can keep track of past entries.
>
> Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)
>
> Enter this in cell D4 and then in Tools>Options>Calculation check
> Iterations and set to 1.
>
> Now when you change the number in C4, D4 will accumulate.
>
> Note 1. If C4 is selected and a calculation takes place anywhere in
> the Application D4 will update even if no new number is entered in
> C4. NOT GOOD.
> Note 2. This operation is not recommended because you will have no
> "paper trail" to follow. Any mistake in entering a new number in C4
> cannot be corrected. NOT GOOD.
>
> To clear out the accumulated total in D4 and start over, select D4
> and Edit>Enter.
>
> Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
> which does what you want without the re-calculation problem, but
> again there is no "paper trail" for back-checking in case of errors
> in data input.
>
> http://longre.free.fr/english/func_cats.htm
>
>
> Gord Dibben Excel MVP
>
>
>
>
> On Fri, 9 Jun 2006 13:15:02 -0700, Paula_p <[email protected]>
> wrote:
>
> >I found the info. on the website very helpful. However, there is one problem
> >i cant seem to fix. Each time i enter the new figure it is added to the
> >existing figure i want, however if i should shift between worksheets or
> >change data in another cell which has no connection to the two columns im
> >using, the new figure keeps adding itself again and again. Example if i
> >overwrite 30 and my new figure is 70 if i edit other cells, the new figure
> >changes to 100, and if i do other changes it will change to 130 ans so on.
> >How do i correct this?
> >
> >"Peo Sjoblom" wrote:
> >
> >> http://www.mcgimpsey.com/excel/accumulator.html
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >> Excel 95 - Excel 2007
> >> Northwest Excel Solutions
> >> www.nwexcelsolutions.com
> >> "It is a good thing to follow the first law of holes;
> >> if you are in one stop digging." Lord Healey
> >>
> >>
> >> "Paula_p" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi everyone,
> >> > I'm trying to create an inventory sheet where i have a correct record of
> >> > goods available for sale.
> >> >
> >> > What i want is to be able to overwrite figures in a particular column and
> >> > have that added to figures in another column. For example. Say i have 25
> >> > Cds
> >> > and i sold 10, i now have 15 availible for sale. However, if i bought 50
> >> > more, i should now have 65. Is there a way i can write the figure bought
> >> > in a
> >> > cell and have that added to the figure in the existing stock?
> >> >
> >> > Also, each time stock for a particular product is bought it is recorded in
> >> > the same cell, ie. the old figure is overwritten, so the 25 is replaced
> >> > with
> >> > 50.
> >> > Thanks.
> >>
> >>
> >>
>
> Gord Dibben MS Excel MVP
>

[Paula_p]

So, is what im trying to do impossible? If i set the number of iterations to
1, why is it iterating more than once when im working on other cells? If this
happens, then what realy is the purpose of setting an iteration number?

"Paula_p" wrote:

> I was using the circular refference. D4 does not update when i input a number.
>
> "Gord Dibben" wrote:
>
> > Paula
> >
> > Which of JE's methods did you use? The VBA or the Circular Reference.?
> >
> > Are you sure you want to do this?
> >
> > Think about it after reading the following.
> >
> > You can have a cumulative total in a cell if you have a
> > separate source cell for adding a new total to the original.
> >
> > Use at your own risk. I am Posting this just to show you how it can
> > be done, not as a good solution. You would be much better off to
> > have another column so you can keep track of past entries.
> >
> > Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)
> >
> > Enter this in cell D4 and then in Tools>Options>Calculation check
> > Iterations and set to 1.
> >
> > Now when you change the number in C4, D4 will accumulate.
> >
> > Note 1. If C4 is selected and a calculation takes place anywhere in
> > the Application D4 will update even if no new number is entered in
> > C4. NOT GOOD.
> > Note 2. This operation is not recommended because you will have no
> > "paper trail" to follow. Any mistake in entering a new number in C4
> > cannot be corrected. NOT GOOD.
> >
> > To clear out the accumulated total in D4 and start over, select D4
> > and Edit>Enter.
> >
> > Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
> > which does what you want without the re-calculation problem, but
> > again there is no "paper trail" for back-checking in case of errors
> > in data input.
> >
> > http://longre.free.fr/english/func_cats.htm
> >
> >
> > Gord Dibben Excel MVP
> >
> >
> >
> >
> > On Fri, 9 Jun 2006 13:15:02 -0700, Paula_p <[email protected]>
> > wrote:
> >
> > >I found the info. on the website very helpful. However, there is one problem
> > >i cant seem to fix. Each time i enter the new figure it is added to the
> > >existing figure i want, however if i should shift between worksheets or
> > >change data in another cell which has no connection to the two columns im
> > >using, the new figure keeps adding itself again and again. Example if i
> > >overwrite 30 and my new figure is 70 if i edit other cells, the new figure
> > >changes to 100, and if i do other changes it will change to 130 ans so on.
> > >How do i correct this?
> > >
> > >"Peo Sjoblom" wrote:
> > >
> > >> http://www.mcgimpsey.com/excel/accumulator.html
> > >>
> > >> --
> > >>
> > >> Regards,
> > >>
> > >> Peo Sjoblom
> > >>
> > >> Excel 95 - Excel 2007
> > >> Northwest Excel Solutions
> > >> www.nwexcelsolutions.com
> > >> "It is a good thing to follow the first law of holes;
> > >> if you are in one stop digging." Lord Healey
> > >>
> > >>
> > >> "Paula_p" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > Hi everyone,
> > >> > I'm trying to create an inventory sheet where i have a correct record of
> > >> > goods available for sale.
> > >> >
> > >> > What i want is to be able to overwrite figures in a particular column and
> > >> > have that added to figures in another column. For example. Say i have 25
> > >> > Cds
> > >> > and i sold 10, i now have 15 availible for sale. However, if i bought 50
> > >> > more, i should now have 65. Is there a way i can write the figure bought
> > >> > in a
> > >> > cell and have that added to the figure in the existing stock?
> > >> >
> > >> > Also, each time stock for a particular product is bought it is recorded in
> > >> > the same cell, ie. the old figure is overwritten, so the 25 is replaced
> > >> > with
> > >> > 50.
> > >> > Thanks.
> > >>
> > >>
> > >>
> >
> > Gord Dibben MS Excel MVP
> >

[Gord Dibben]

Paula

Is this the method you're attempting?

You say D4 does not update when you plug a new number into C4.

Is your calculation mode set for automatic or manual?


Gord

On Mon, 12 Jun 2006 11:49:02 -0700, Paula_p <[email protected]>
wrote:

>Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)
>>
>> Enter this in cell D4 and then in Tools>Options>Calculation check
>> Iterations and set to 1.
>>
>> Now when you change the number in C4, D4 will accumulate.

[Paula_p]

Its on automatic. What does manual do? I tried changing it to that but it
doesn't work either.

"Gord Dibben" wrote:

> Paula
>
> Is this the method you're attempting?
>
> You say D4 does not update when you plug a new number into C4.
>
> Is your calculation mode set for automatic or manual?
>
>
> Gord
>
> On Mon, 12 Jun 2006 11:49:02 -0700, Paula_p <[email protected]>
> wrote:
>
> >Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)
> >>
> >> Enter this in cell D4 and then in Tools>Options>Calculation check
> >> Iterations and set to 1.
> >>
> >> Now when you change the number in C4, D4 will accumulate.
>
>

[bobbycheever]

Thanks for the above posts. I was needing the same solution. I used the formula supplied above "=IF(CELL("address")="$C$4",C4+D4,D4)" and it works fine, however I'm having trouble copying it down the column. It is probably because I don't completely understand the formula. Can anyone help me with that?