Stock Count. Adding to a pile
 

Greetings,

I'm trying to organize my companies stock a little bit more
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 to
the pile. Lets say I have 200 books. I type 50 in this one cell and
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!


--
tipsyPosted from http://www.pcreview.co.uk/ newsgroup access

Take a look at this:

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

Biff

"tipsy" <tipsy.1rf98f@ wrote in message
...

Greetings,

I'm trying to organize my companies stock a little bit more
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 to
the pile. Lets say I have 200 books. I type 50 in this one cell and
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!


--
tipsyPosted from http://www.pcreview.co.uk/ newsgroup access

Thanks! I got it all in. But there is one thing with this system that
is bad. If you aren't paying attention you could end up screwed up the
count. I'm just wondering if I can extend my pile column into another
cell but that cell states the previous number that was in the pile cell
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. And
anything into c1 will update d1's pile. Could I get lets say e1 to
display what d1 had before i typed a new number into c1 (incase of a
typo)


--
tipsyPosted from http://www.pcreview.co.uk/ newsgroup access

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" <tipsy.1rgkcy@ wrote in message
...

Thanks! I got it all in. But there is one thing with this system that
is bad. If you aren't paying attention you could end up screwed up the
count. I'm just wondering if I can extend my pile column into another
cell but that cell states the previous number that was in the pile cell
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. And
anything into c1 will update d1's pile. Could I get lets say e1 to
display what d1 had before i typed a new number into c1 (incase of a
typo)


--
tipsyPosted from http://www.pcreview.co.uk/ newsgroup access

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


--
tipsyPosted from http://www.pcreview.co.uk/ newsgroup access

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

On Thu, 30 Jun 2005 23:01:11 +0100, tipsy <tipsy.1rgm7o@ wrote:


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