Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Stock Quote to EXCEL | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
SUM or COUNT and adding a row | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |