Is there a way to keep the current total when resetting/deleting cells?

[PlumGr8]

So i have a formula for counting even that have occurred at certain sites. Like different job sites doing different events and a simple one that basically says

Job site 1 did this
Job site 2 did that

we delete everything older then 2 months as the year progresses, just to keep file sizes smaller. Is there a way to create a formula that will maintain the overall total though? So, that say there was 450 events, and we delete 2 months worth, that the total wont decrease and will always maintain and increase its value? I don't think there is, but this site is awesome for help so couldn't hurt to ask.

 

[Trevor_S]

A formula will recalculate based on what is on the spreadsheet, rather than what was once on it. Is the solution that when two months are deleted, they are replaced by a summary line with a value (not formula) for the total number of records in that period. Your main total could then count the remaining records and add the summary lines.

You could automate the deletion of old records and increase the deleted records count using a macro which runs either on clicking a button or each time the spreadsheet is opened.

 

[PlumGr8]

Sorry for the delay. So i am "meh" with excel, can figure out most i need. BUT macros etc is still above me. So this is my formula

=COUNTIF(G2:G500, "Job Site 1")+COUNTIF(H2:H500, "Job Site 1") So that any time that is entered it just adds to a tally counter. So how do i create a macro that will auto delete say ever 60 or 90 days while also maintaining the previous total?

 

[BrandonBerner]

This is over my head too, but perhaps this will help...
https://www.mrexcel.com/forum/excel-questions/1086728-clear-cell-after-60-seconds-vba.html

 

[PlumGr8]

Hmmm, clear as mudd? Lol. I need to read up in Macros better, but hopefully i'll get some other responses as well. This site is awesome for stuff

 

[Trevor_S]

Happy to write it, but I'm away this week without access to Excel. Will write it when I'm back, if no one has done it by then. Just a few questions:
- I assume that you also have a similar formula for job site 2, also looking at columns g and h? What cells are the formulae currently in, and what is the sheet (tab) name?
- What column is the date in (so the macro can look for the rows that are old enough to be deleted)?
- The formulas go to row 500 - is that because its a high enough number to always cover the last 60/90 days?
- The macro would need a spare cell per job site - this would store the count for the rows that have been deleted, which would then be added to the totals in the existing formulae.

 

[PlumGr8]

Would be awesome if you can, been more than helpful already

yes, its the same formula across job site 1, 2, 3 all referencing G & H. The Formula is currently in column M. Row 10? I'd have to go double check. But its approx M10, M11 & M12.
the date is in column A
I have the formula defaulted to row 500. Mostly because i am not always here to maintain the files and keep it limited to 2 to 3 months, so i need to make sure that it coverers enough cells to keep up. I actually think on the main file i have it down to row 5000.
And largely anything Column L 10 and over is fair game. I can always adapt that part as needed. I just suck at writing macros/VB's.

 

[Trevor_S]

Thanks. So if you've got the existing formulae in columns to the right of the data, when you're deleting records, you're not deleting the entire rows, otherwise there's a risk that you'll delete your formulae? Presumably you're just deleting the row for a range of columns? If so, what's the last column in that range?

 

[PlumGr8]

Correct, when i do it i just delete the data and shift rows up. I haven't "yet" had a problem with people deleting formulas i have entered. Once i get this updated i plan to lock those cells and protect the sheet as well.

all the data is entered across columns A to K. L and over is used for cell drop down info etc.

 

[Trevor_S]

The macro below should work. I'm using cells N10 to N12 to store the total number of Job Site entries that have been deleted, so the formulae in M10 to M12 need to be changed to add in the values in N10 to N12:

M10 is now =COUNTIF(G:G,"Job Site 1")+COUNTIF(H:H,"Job Site 1")+N10
M11 is now =COUNTIF(G:G,"Job Site 2")+COUNTIF(H:H,"Job Site 2")+N11
M12 is now =COUNTIF(G:G,"Job Site 3")+COUNTIF(H:H,"Job Site 3")+N12


I've taken the row numbers out of the formulae, otherwise they would just get lower every time that old records were deleted. So now the whole column is being checked.

Sub DeleteOldRecords()
DaysOld = InputBox("Delete entries more than this many days old", "Delete old records")
If IsNumeric(DaysOld) = False Or DaysOld = False Then
    'Either non-numeric / blank entered, or user cancelled
    MsgReply = MsgBox("No records deleted.", vbOKOnly, "Delete old records")
    End
End If
CheckRow = 2 'This is the first row to check - it assumes that row 1 is a header
RecordsFound = 0 'This is a counter of the number of rows deleted
        
Do  'Go through records, if the record is old enough to delete then before deleting, check for job sites in cols G and H and update the value in col N
    'Stop when column A is blank
    If Range("A" & CheckRow).Value = "" Then Exit Do
    If Range("A" & CheckRow).Value < (Now - DaysOld) Then
        'This record needs to be deleted - check if it contains any Job Sites and update the totals
        If Range("G" & CheckRow).Value = "Job Site 1" Then Range("N10").Value = Range("N10").Value + 1
        If Range("H" & CheckRow).Value = "Job Site 1" Then Range("N10").Value = Range("N10").Value + 1
        If Range("G" & CheckRow).Value = "Job Site 2" Then Range("N11").Value = Range("N11").Value + 1
        If Range("H" & CheckRow).Value = "Job Site 2" Then Range("N11").Value = Range("N11").Value + 1
        If Range("G" & CheckRow).Value = "Job Site 3" Then Range("N12").Value = Range("N12").Value + 1
        If Range("H" & CheckRow).Value = "Job Site 3" Then Range("N12").Value = Range("N12").Value + 1
        'Then delete the record
        Range("A" & CheckRow & ":K" & CheckRow).Delete (xlShiftUp)
        RecordsFound = RecordsFound + 1
    Else
        CheckRow = CheckRow + 1
    End If
Loop
   
MsgReply = MsgBox(RecordsFound & " records have been deleted.", vbOKOnly, "Delete old records")
End Sub

When run, the macro will ask how many days old you want to delete prior to. It will then work down column A (starting from row 2 because I'm assuming that you have a header row) until the first blank cell in column A (which it will assume is the end of the list of records). If the date is old enough for deletion, it counts the number of each job sites, adds to the appropriate rows in column N and deletes the cells.