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]

In my macros:

Column W should contain a formula that counts the number of occurrences of the item within the remaining data, and adds the value of AG. It doesn't need to take account of the value off AA.

Column AG should be the number of occurrences of that item within the rows that have already been deleted. This is updated by the macros.

Column AA is the value of the formula in the corresponding row in column W. The only reason for this is that we can only tell that a record has been deleted when a change results in the value of one of the formulae in column W reducing. To know if the value has reduced, we need to know what it was before the change - which is why we're regularly updating column AA with values of column W. If a change occurs and the value of any row in column W is less than the corresponding row in column AA, the difference is the number of records deleted. This needs to be added to the value in column AG.

The Difference... values: this is just a way of storing a calculation done in one part of the macro, for use in the next. The first block where the "Difference..." variables appear, we're working out the difference between column W (the calculated count of items as of "now") and column AA (what column W had been before the latest change). We have to do this once for every item/row that you're keeping track of. In your very original post that was 3 job sites - but in the current sheets we're dealing with 12 items. The second block where the "Difference..." variables appear, we are looking at their value to see if rows have been deleted that affect that item - and if they have, we're increasing the value of deleted items as recorded in AG.

Unfortunately I can't test it myself at the moment as I haven't got access to Excel (I'm typing this from a phone) - but I'll try to put a demo spreadsheet together next week. In the meantime, it would be interesting to know what formula you have in the cells in column W. The formula shouldn't refer to column AA at all - but at the moment (without being able to test) I can't think what else may be causing the issues you've described.

 

[PlumGr8]

OK, so one part i ma trying to work through. Thanks for being patient haha. So yes it did start with 3 job sites. And that is all working beautifully so far. But this 2nd macro is largely an independent tracking that i am trying to do. and only has to deal with values in column H & K. Column H is which Job Site it is at, 1, 2 or 3. K is where i am entering "notes", in these examples 200, 400, 2000 etc. So 4 separate formulas for tracking MX across all 3 sites. IF it will be easier, shoot me an email. I'll strip off some of the info that i need to, but leave the overall format setup. If that would be best overall shoot me an email

aaron.vogel69@gmail.com

Maybe visually seeing what i am trying to setup will also help. I was trying to insert some images but it was being silly yesterday.