Stock Count. Adding to a pile

[tipsy]

Greetings,

I'm trying to organize my companies stock a little bit mor
effectively. Now I'm a rank amateur when it comes to excel.

I'm trying to get a cell that when i type a number into it it'll add t
the pile. Lets say I have 200 books. I type 50 in this one cell an
the pile is now 250. I click that 50 and delete it.. put in 50 more.
Then I have 300 now. I also need this for subtraction.

Thanks for the help

 

[Biff]

Take a look at this:

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

Biff

 

[tipsy]

Thanks! I got it all in. But there is one thing with this system tha
is bad. If you aren't paying attention you could end up screwed up th
count. I'm just wondering if I can extend my pile column into anothe
cell but that cell states the previous number that was in the pile cel
before it was changed. so far I'm working with...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
If .Address(False, False) = "C1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("D1").Value = Range("D1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

Which means anything I enter into a1 will update b1's pile. An
anything into c1 will update d1's pile. Could I get lets say e1 t
display what d1 had before i typed a new number into c1 (incase of
typo

 

[Guest]

Hi

You have found the problem with the accumulator method - there is no track
of what you have typed! I would suggest using a single column to enter your
data - and if it screws up you can see where the problem is and sort it out.

Andy.

 

[tipsy]

You would think that they could easily correct this with the variable
to record previous answer

 

[Gord Dibben]

Who are "they"?

And why do you think it would be easy?

You could use a worksheet_change event macro to enter the contents of the
input cell into column B at the next available empty row.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
If Target.Address = "$A$2" And Target.Value <> "" Then
ActiveSheet.Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
End Sub

Right-click on your sheet tab and select "View Code". Copy the above code
into the module that opens.

Using A2 as the input cell, any new number entered will be automatically
placed into Column B(starting at B2)at the next available empty row.

I would enter in C2 =Sum(B:B) or =Sum(B2:B500). Whatever you think you need
to gather all future values in Column B.

Now you have a "paper trail" in column B and a Totalizer cell(C2)

Note: if a mistake is made in last entered number in A2 , you will have to
delete the contents of the last cell in Column B then re-enter in A2.


Gord Dibben Excel MVP