excel formula to automatically update number

[Really Confused]

Hi
Can anyone tell me is there a formula for updating a number in a cell?
At work we enter data into a cell, but when a new item comes in we have to add the number of items to the number already in the cell such as; if there are already 50 items in stock we have to manually add 25 to 50 to receive the correct amount of 75 items now in stock.

 

[T. Valko]

Hi
Can anyone tell me is there a formula for updating a number in a cell?
At work we enter data into a cell, but when a new item comes in we have to add the number of items to the number already in the cell such as; if there are already 50 items in stock we have to manually add 25 to 50 to receive the correct amount of 75 items now in stock.

This is not a good idea.

If you make a mistake entering the number you may not notice it and there is no record to audit that will let you know that you did make a mistake.

With that being said, see this:

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

 

[DragonWood]

Do you want a formula or VBA code?

 

[Really Confused]

Thank you for answering back, sorry I'm not sure what a VGA code is. What is the difference between a code and formula?

 

[DragonWood]

VGA code is basically Macros.

The difference is a formula is entered into the cells on your worksheet. The VBA code is entered in the background.

The link provided by T. Valko is an example of a code that would work. You would just need to make some changes to make it fit your workbook.

If you want to use a formula you're going to need more than one cell in order to make it work right.

 

[Really Confused]

I knew I would need more colums to make the formula, but was not sure how to set it up correctly.

Sorry Dragon I don't see the link you suggested.

 

[VoG]

An alternative would be Tools > Options > Calculation > Iterations = 1 and set the cell to refer to itself (yuck!).

 

[DragonWood]

Actually, T. Valko suggested the link. I recommend you at least read up on it. Ideally, VBA is the way to do this because then you can provide an entry point for the items.

However, if you want to use a formula and can use whatever cells you want you can do something like this:

Column A = Item Names
Column B = Items Received
Column C = Items Sold
Column D = Items In Stock
Column E = Inventory Start

Although the titles should be self-explanatory, I’ll give a quick rundown anyway.

The item name goes in column A, the amount you had at the last inventory goes in column E. This formula goes in column D:

  =IF(E2="","",((E2+B2)-C2))

The rest is filled in as applicable.