How to keep a running 5 cell total

P

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
145
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 
Sort by date Sort by votes
dreid1011 said:
Okay, so this is what you have for the first book:

Book1
ABCDE
5Truck 1Truck 2Truck 3Truck 4
655
713
81013
9522
10215175
111000150020005055
12Current Hours215175
13Total1002151520175060
Sheet5
Cell Formulas
RangeFormula
B13:E13B13=B11+B12


Does this look like what you have for the second?

Book1
B
10Truck 1
111000
1215
131015
14Truck 2
15950
1610
17960
18Truck 3
191125
205
211130
Sheet6
Cell Formulas
RangeFormula
B13,B21,B17B13=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.
Click to expand...
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.
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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?
 
Upvote 0
PlumGr8 said:
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
Click to expand...
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.

VBA Code: 
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
 
Upvote 0
dreid1011 said:
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
Click to expand...
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.
 
Upvote 0
PlumGr8 said:
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.
Click to expand...
Here is the code adjusted to target only column B for those selected rows, 12, 16, 20, etc.
VBA Code: 
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
 
Upvote 1
Solution
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.

VBA Code: 
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
 
Upvote 0
dreid1011 said:
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.

VBA Code: 
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
Click to expand...
Code adjusted to ignore rows less than 12.

VBA Code: 
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
 
Upvote 0
dreid1011 said:
Here is the code adjusted to target only column B for those selected rows, 12, 16, 20, etc.
VBA Code: 
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
Click to expand...
Yep, that appears to be exactly what i needed. Thanks a lot for all the help.
 
Upvote 0
dreid1011 said:
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.

VBA Code: 
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
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Formula to calculate how much to add or subtract to keep totals even
Replies
7
Views
168
MrHoyt
M
B
  • Question Question
Event code to populate message upon workbook open at specified dates
Replies
2
Views
204
bh24524
B
S
  • Question Question
Macro to add sum total to track percentages
Replies
0
Views
334
spenserallen7
S
N
  • Solved
How to maintain font when referencing another cell?
Replies
4
Views
440
ned lud
N
M
  • Question Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
1
Views
437
bobaol
B

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top