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]

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?

 

[dreid1011]

Awesome that one works

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?

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.