How to keep a running 5 cell total

[PlumGr8]

Wasn't sure the best way to word it. But i am tryign to find a way to create and "average" of our hours over the past 5 or 10 deliveries, but also auto updates. we have a prior hours, driven hours then total hours. Say B11, 12, 13. So B11 would show 1000 hrs, B12 would say 15 hours, B13 would show 1015 hours. Every day we update the hours in B12 for todays value. But is there a way to keep a running previous total? Like the last 5 days say B6 - B10 will keep the most recent enteries while deleteing the previous enteries? We do this on another sheet to get an averge time per truck, but trying to see if i can combine it all on to 1 page.

 

[dreid1011]

So i was just trying that one, but that is targeting all row 16, 20, etc but there is also other data in general in some of those columns, so it still have to be targeting specificall B12, B16 etc. The previous VBA seemed to be working pretty well, just not sure how to include B16, B20 etc following the B12 commands. Been trying a few things but no luck just yet. I guess i could do multiple VBAs if need be? But hoping there's a simple way to include them all into one.

Here is the code adjusted to target only column B for those selected rows, 12, 16, 20, etc.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count <= 1 Then
Select Case Target.Address
    Case Range("B12").Address
        Range("M9").Value = Range("M8").Value
        Range("M8").Value = Range("M7").Value
        Range("M7").Value = Range("M6").Value
        Range("M6").Value = Range("M5").Value
        Range("M5").Value = Target.Value
    Case Range("B16").Address
        Range("N9").Value = Range("N8").Value
        Range("N8").Value = Range("N7").Value
        Range("N7").Value = Range("N6").Value
        Range("N6").Value = Range("N5").Value
        Range("N5").Value = Target.Value
    Case Range("B20").Address
        Range("O9").Value = Range("O8").Value
        Range("O8").Value = Range("O7").Value
        Range("O7").Value = Range("O6").Value
        Range("O6").Value = Range("O5").Value
        Range("O5").Value = Target.Value
    Case Else
        GoTo ReEnableEvents
End Select
End If
ReEnableEvents:
Application.EnableEvents = True
End Sub

 

[dreid1011]

Wasn't sure the best way to word it. But i am tryign to find a way to create and "average" of our hours over the past 5 or 10 deliveries, but also auto updates. we have a prior hours, driven hours then total hours. Say B11, 12, 13. So B11 would show 1000 hrs, B12 would say 15 hours, B13 would show 1015 hours. Every day we update the hours in B12 for todays value. But is there a way to keep a running previous total? Like the last 5 days say B6 - B10 will keep the most recent enteries while deleteing the previous enteries? We do this on another sheet to get an averge time per truck, but trying to see if i can combine it all on to 1 page.

You can do that, but it would likely have to be done with some VBA.

 

[dreid1011]

You can do that, but it would likely have to be done with some VBA.

Maybe something like this, in the sheet module for your main sheet:

Edited to add a line to update B13 with the new B12 value as well.

The most recent value is entered into B10 and the values move up the sheet. This can be rearranged if you want the most recent value entered into B6 and move down.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B12")) Is Nothing Then
    Range("B6").Value = Range("B7").Value
    Range("B7").Value = Range("B8").Value
    Range("B8").Value = Range("B9").Value
    Range("B9").Value = Range("B10").Value
    Range("B10").Value = Range("B12").Value
    Range("B13").Value = Range("B13").Value + Range("B12").Value
End If
End Sub

 

[PlumGr8]

Ok, so trying to tweak it. For B13 all i usually have it =B11+B12. And if/when i want to add more truck, like if i want to to the same setup but in the C column, can i add it below the B section? Obviously not the greatest wth VBA, i can tweak things as i get going ok, just usually need help with the inital setup on this stuff. Thanks a lot, working as needed so far, just need to tweak those couple things.

 

[PlumGr8]

I tried tweaking it, for the inital change, think this looks good, am i correct?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B12")) Is Nothing Then
Range("B6").Value = Range("B7").Value
Range("B7").Value = Range("B8").Value
Range("B8").Value = Range("B9").Value
Range("B9").Value = Range("B10").Value
Range("B10").Value = Range("B12").Value

End If
End Sub

 

[dreid1011]

Ok, so trying to tweak it. For B13 all i usually have it =B11+B12. And if/when i want to add more truck, like if i want to to the same setup but in the C column, can i add it below the B section? Obviously not the greatest wth VBA, i can tweak things as i get going ok, just usually need help with the inital setup on this stuff. Thanks a lot, working as needed so far, just need to tweak those couple things.

I tried tweaking it, for the inital change, think this looks good, am i correct?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B12")) Is Nothing Then
Range("B6").Value = Range("B7").Value
Range("B7").Value = Range("B8").Value
Range("B8").Value = Range("B9").Value
Range("B9").Value = Range("B10").Value
Range("B10").Value = Range("B12").Value

End If
End Sub

You don't want B13 to be a running total? Just the B11 + the current hours entered into B12?

And you want to add another column that does the same stuff?

 

[PlumGr8]

It is a running total, but as things change with trucks, engines etc we have to change the previous and total. Typically when we update the total we enter B13 's total in B11's, and the hours for the days in B12's to give the new total. I've been trying it, i "think" that change i did is working as needed, just not sure how to expand it to other columns as needed.

 

[dreid1011]

Try this, you can expand it for as many columns as you want to add by adjusting the Range("B12:E12") to whatever column you need in the following line:

ie: For 5 trucks, the range would be B12:F12, etc.

If Not Intersect(Target, Range("B12:E12")) Is Nothing And Target.Count <= 1 Then

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B12:E12")) Is Nothing And Target.Count <= 1 Then
    Cells(6, Target.Column).Value = Cells(7, Target.Column).Value
    Cells(7, Target.Column).Value = Cells(8, Target.Column).Value
    Cells(8, Target.Column).Value = Cells(9, Target.Column).Value
    Cells(9, Target.Column).Value = Cells(10, Target.Column).Value
    Cells(10, Target.Column).Value = Cells(12, Target.Column).Value
End If
End Sub

 

[PlumGr8]

Awesome that one works

Try this, you can expand it for as many columns as you want to add by adjusting the Range("B12:E12") to whatever column you need in the following line:

ie: For 5 trucks, the range would be B12:F12, etc.

If Not Intersect(Target, Range("B12:E12")) Is Nothing And Target.Count <= 1 Then

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B12:E12")) Is Nothing And Target.Count <= 1 Then
    Cells(6, Target.Column).Value = Cells(7, Target.Column).Value
    Cells(7, Target.Column).Value = Cells(8, Target.Column).Value
    Cells(8, Target.Column).Value = Cells(9, Target.Column).Value
    Cells(9, Target.Column).Value = Cells(10, Target.Column).Value
    Cells(10, Target.Column).Value = Cells(12, Target.Column).Value
End If
End Sub

i should be able to adjust from there. Now one last thing, as i try to organize the mess of these workbooks. They have another book that is similar, but instead of columns specific, its by rows.

So the normal entry would be B11, B12, B13. Then the next truck may be B15, B16, B17. We have no data past column M i think, so would i be easier to move the target from B12 to say M6, M7, etc. Then the next vehicl be N6, N7, etc?