Fixed running total

[bizmark222]

Hi
I know how to total sum cells together but-
I need for example, if f5,f6,f7 = 1 (f5 has 1 in it) and i change the value in f5 back to 0 the sum total stay fixed at 1, so if f6 was to show 3 it would =4 and keep an ongoing total regardless of numbers changing back to 0 in above cells, make sense?

 

[bizmark222]

Cross posted https://www.excelforum.com/excel-formulas-and-functions/1279807-fixed-running-total.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

sorry, i will concentrate on this thread

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KyCl As Range, KyCls As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   Set KyCl = Range("F5:F7")
   On Error Resume Next
   Set KyCls = Union(KyCl, KyCl.Precedents)
   On Error GoTo 0
   If Not Intersect(Target, KyCls) Is Nothing Then
      Range("F8") = Range("F8").Value + Intersect(Target.Dependents, KyCl).Value
   End If
End Sub

 

[bizmark222]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KyCl As Range, KyCls As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   Set KyCl = Range("F5:F7")
   On Error Resume Next
   Set KyCls = Union(KyCl, KyCl.Precedents)
   On Error GoTo 0
   If Not Intersect(Target, KyCls) Is Nothing Then
      Range("F8") = Range("F8").Value + Intersect(Target.Dependents, KyCl).Value
   End If
End Sub

Thank you very much, that is just what I was looking for

 

[Fluff]

You're welcome & thanks for the feedback

 

[bizmark222]

They are looking at hard values on same sheet, thanks

They are looking at hard values that are loaded from a trading software.
It was working when testing manually but when values are loaded, it will not add them again in cell F8

 

[Fluff]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KyCl As Range, KyCls As Range, Cl As Range

   Set KyCl = Range("F5:F7")
   On Error Resume Next
   Set KyCls = Union(KyCl, KyCl.Precedents)
   On Error GoTo 0
   If Not Intersect(Target, KyCls) Is Nothing Then
      Range("F8") = Range("F8").Value + Application.Sum(Intersect(Target.Dependents, KyCl).Value)
   End If
End Sub

 

[bizmark222]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KyCl As Range, KyCls As Range, Cl As Range

   Set KyCl = Range("F5:F7")
   On Error Resume Next
   Set KyCls = Union(KyCl, KyCl.Precedents)
   On Error GoTo 0
   If Not Intersect(Target, KyCls) Is Nothing Then
      Range("F8") = Range("F8").Value + Application.Sum(Intersect(Target.Dependents, KyCl).Value)
   End If
End Sub

 

[bizmark222]

Picture shows column AA being used. As soon as a value hits 1, total just keeps going up and up and not showing the true sum (even with 6 zeros and one 1)

 

[Fluff]

total just keeps going up and up and not showing the true sum (even with 6 zeros and one 1)

It does that because that's what you asked for.

 

[bizmark222]

I wanted a record of the 1s added that appear above in the AA Column. What happens is as soon as a one appears, the total just keeps adding numbers from a different source. Even if 1 gets triggered and stays fixed and others stay as 0, the sum continually keeps adding numbers that are not appearing in the above cells?
It works until linked to the software.

I recorded a clip but don't know how to share it here.