I created a Google Spreadsheet that is made up of 20+ sheets (all formatted the same way). The company I work for loads containers. One of those sheets calculates a particular parameter on each of those 20+ sheets (
countifs formula) and gives me a total value of how many containers were loaded on that day. For example, say on 11/20/14 we loaded 5 containers on sheet 1, sheet 2 = 6 containers, sheet 3 = 7 containers... etc. It is going to calculate how many total containers were loaded on 11/20/14 and spit out a number. Up to this part I am OK.
The problem is that these sheets need to be cleared and reused once we're finished loading on a sheet. What that means is that when a sheet is processed and cleared, any value attached to the parameter mentioned above is taken away. So if my total value is 20 containers loaded on 11/20/14, and I clear 5 containers on sheet 1, now my value will go down to 15 containers. But in reality, it was 20.
I've tried copying and pasting the end value at the beginning of the next day, before a sheet is processed and cleared, but sometimes, things get out of sync when a sheet is processed and cleared before the beginning of the next day.
Is there any way to have a value automatically set itself after "today" and no longer be associated with the formula?
Again, working with Google Spreadsheet. I'll need step by step help implementing a script if that is the answer.