Cell change to Accumulator total
Posted by Jean Airey on January 18, 2001 7:39 AM
Based on an input selection in one cell, the number in another cell changes.
I want to have a third cell automatically reflect the total of the numbers that have appeared in the second cell.
Can this be done?
(Looking at running in both '97 and '00)
Posted by Aladin Akyurek on January 18, 2001 10:37 AM
Who knows, you might be able to adapt Bullen's technique which makes use of circular reference to your problem. See his file 'lastchng.xls' at:
http://www.BMSLtd.co.uk
Aladin
Posted by Dave Hawley on January 18, 2001 6:43 PM
Hi Jean
If you don't want a circular reference
then you could place some code like this in your worksheet module. Right click on the sheet name tab and select "View Code" then paste this code in.
Dim Val
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Address = "$A$1" And IsNumeric(Target) Then
Application.EnableEvents = False
Target = Target + Val
Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Address = "$A$1" And IsNumeric(Target) Then
Application.EnableEvents = False
Val = Target
Application.EnableEvents = True
End If
End Sub
Change the address to suit and push Alt+Q then save.
Dave
- OzGrid Business Applications
