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.

 

[PlumGr8]

Okay, so this is what you have for the first book:

Book1
	A	B	C	D	E
5		Truck 1	Truck 2	Truck 3	Truck 4
6		55
7		13
8		10			13
9		5			22
10		2	15	17	5
11		1000	1500	2000	5055
12	Current Hours	2	15	17	5
13	Total	1002	1515	2017	5060
Sheet5

Cell Formulas
Range	Formula
B13:E13	B13	=B11+B12

Does this look like what you have for the second?

Book1
	B
10	Truck 1
11	1000
12	15
13	1015
14	Truck 2
15	950
16	10
17	960
18	Truck 3
19	1125
20	5
21	1130
Sheet6

Cell Formulas
Range	Formula
B13,B21,B17	B13	=B11+B12

I am not quite sure what you mean by changing the target to M6, M7 etc. Is that where you would update the current hours then? If my example above matches, then with code, the target would be something like B12,B16,B20.

The target in the code is which cell you are updating with the current hours, so whatever cells those are, that would be the target range.

Yep that 2nd image is exactly what i was looking for. The only reason i mentioned moving those 5 rotating values is basically to keep them aside and out if sites, but that i can reference those 5 values to keep a running average. And if i had them in columns M, N etc if needed ai could expand the numbers to give me a longer average time without have to have them hidden on the side of the page everyone uses. Basically out of site out of mind type thing.

 

[PlumGr8]

Like evertime i'd update B12, or B16, it would move the number over to M5, or N5, etc. So on that side of the page i could keep 5 numbers, 10 numbers etc and have those columsn hidden but continuing to update as needed

 

[PlumGr8]

I did just try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b12")) Is Nothing Then
Range("m6").Value = Range("m7").Value
Range("m7").Value = Range("m8").Value
Range("m8").Value = Range("m9").Value
Range("m9").Value = Range("m10").Value
Range("m10").Value = Range("b12").Value

End If
End Sub


That seems to do what i was trying to do, but getting stuck on how to add a section for the addional cells. Would i just change the targe from B12 to say B15 and continue down as needed?

 

[dreid1011]

Like evertime i'd update B12, or B16, it would move the number over to M5, or N5, etc. So on that side of the page i could keep 5 numbers, 10 numbers etc and have those columsn hidden but continuing to update as needed

I can't come up with a nice way to make this as easily expandable as the other code at the moment, but this should get the job done for now. To add more trucks, you will have to add more cases to the Select Case structure and adjust the column letters appropriately.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count <= 1 Then
Select Case Target.Row
    Case 12
        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 16
        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 20
        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

 

[PlumGr8]

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Count <= 1 Then Select Case Target.Row Case 12 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 16 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 20 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

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.

 

[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]

Alright, I got it. This version is nicely expandable so long as the format remains exactly as you confirmed. If the target cells in Column B are not in cells whose rows are evenly divisible by 4, then the code will not work for it. I also don't think there is anything you need to adjust to add trucks. The only thing I might mention is that it will also trigger the code on Rows 4 and 8, but I can add to it to block those rows if you'd like.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row Mod 4 = 0 And Target.Column = 2 And Target.Count <= 1 Then
    Cells(9, (Target.Row / 4) + 10).Value = Cells(8, (Target.Row / 4) + 10).Value
    Cells(8, (Target.Row / 4) + 10).Value = Cells(7, (Target.Row / 4) + 10).Value
    Cells(7, (Target.Row / 4) + 10).Value = Cells(6, (Target.Row / 4) + 10).Value
    Cells(6, (Target.Row / 4) + 10).Value = Cells(5, (Target.Row / 4) + 10).Value
    Cells(5, (Target.Row / 4) + 10).Value = Cells(Target.Row, 2).Value
Else
    GoTo ReEnableEvents
End If
ReEnableEvents:
Application.EnableEvents = True
End Sub

 

[dreid1011]

Alright, I got it. This version is nicely expandable so long as the format remains exactly as you confirmed. If the target cells in Column B are not in cells whose rows are evenly divisible by 4, then the code will not work for it. I also don't think there is anything you need to adjust to add trucks. The only thing I might mention is that it will also trigger the code on Rows 4 and 8, but I can add to it to block those rows if you'd like.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row Mod 4 = 0 And Target.Column = 2 And Target.Count <= 1 Then
    Cells(9, (Target.Row / 4) + 10).Value = Cells(8, (Target.Row / 4) + 10).Value
    Cells(8, (Target.Row / 4) + 10).Value = Cells(7, (Target.Row / 4) + 10).Value
    Cells(7, (Target.Row / 4) + 10).Value = Cells(6, (Target.Row / 4) + 10).Value
    Cells(6, (Target.Row / 4) + 10).Value = Cells(5, (Target.Row / 4) + 10).Value
    Cells(5, (Target.Row / 4) + 10).Value = Cells(Target.Row, 2).Value
Else
    GoTo ReEnableEvents
End If
ReEnableEvents:
Application.EnableEvents = True
End Sub

Code adjusted to ignore rows less than 12.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row Mod 4 = 0 And Target.Column = 2 And Target.Row > 11 And Target.Count <= 1 Then
    Cells(9, (Target.Row / 4) + 10).Value = Cells(8, (Target.Row / 4) + 10).Value
    Cells(8, (Target.Row / 4) + 10).Value = Cells(7, (Target.Row / 4) + 10).Value
    Cells(7, (Target.Row / 4) + 10).Value = Cells(6, (Target.Row / 4) + 10).Value
    Cells(6, (Target.Row / 4) + 10).Value = Cells(5, (Target.Row / 4) + 10).Value
    Cells(5, (Target.Row / 4) + 10).Value = Cells(Target.Row, 2).Value
Else
    GoTo ReEnableEvents
End If
ReEnableEvents:
Application.EnableEvents = True
End Sub

 

[PlumGr8]

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

Yep, that appears to be exactly what i needed. Thanks a lot for all the help.

 

[PlumGr8]

Alright, I got it. This version is nicely expandable so long as the format remains exactly as you confirmed. If the target cells in Column B are not in cells whose rows are evenly divisible by 4, then the code will not work for it. I also don't think there is anything you need to adjust to add trucks. The only thing I might mention is that it will also trigger the code on Rows 4 and 8, but I can add to it to block those rows if you'd like.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row Mod 4 = 0 And Target.Column = 2 And Target.Count <= 1 Then
    Cells(9, (Target.Row / 4) + 10).Value = Cells(8, (Target.Row / 4) + 10).Value
    Cells(8, (Target.Row / 4) + 10).Value = Cells(7, (Target.Row / 4) + 10).Value
    Cells(7, (Target.Row / 4) + 10).Value = Cells(6, (Target.Row / 4) + 10).Value
    Cells(6, (Target.Row / 4) + 10).Value = Cells(5, (Target.Row / 4) + 10).Value
    Cells(5, (Target.Row / 4) + 10).Value = Cells(Target.Row, 2).Value
Else
    GoTo ReEnableEvents
End If
ReEnableEvents:
Application.EnableEvents = True
End Sub

I'm going to play with that one as well. I also have to keep it "idiot proof" for when i am not here, people like to change stuff without realizing what they are changing. The earlier one does work well and may be more user friendly for people if they mess something up. But i will check this latest one as well. Thanks again for giving me 2 great solutions.