need help with keeping a result from reverting back to zero

[katmutz]

I'm working on a spreadsheet that will track a number of items worked by 9 different people. Some of the entries are simple. 1 item to be worked, its worked and matter is done. Some items are not so simple. The spreadsheet looks like this:


REPORT NAME # REQUESTED # COMPLETED # TO BE WORKED
359 NAME 359 SUM(O28:BB28) =req -completed


O28:BB28 is the rows where people enter what they did for this report over the course of a week.



so far, so good. The problem I'm having is when the report being worked has a large number of items & needs to be carried over to the next week. They want to be able to erase the current data so its "fresh" for the next week. I tried to convince the person who collects the numbers in this report that it would be easier to carry over the report into the next week section of the spreadsheet, but that idea was vetoed. The idea of manually typing in the # completed at the end of the week was also vetoed. The powers above my pay-grade want it all "automatic" to reduce the current issue of math errors (whole other issue for a different thread!)



So at the end of the week, the data contained in cells O28:BB28 is erased. Unfortunately, that resets the # completed back to 0 and the # to be worked back to the original # requested. I've tried several IF statements that don't work. Is there a formula, trick or something that will "keep" the number completed from going back to zero? Thanks!!

[Hercules1946]

Its rather difficult to advise with the information given. I assume that you have one item per row, and there must be two columns somewhere for "COMPLETED" and "TO BE WORKED" with a suitable marker that you can count, placing two totalsat the bottom of the sheet. This is where I lose track.
When you say you erase data for the following week, this should be after you've taken a copy to avoid losing the previous weeks report? On the following weeks report, (being set up presumablythis week) all the items will be marked "TO BE WORKED", and "COMPLETE" would be 0.
So if you have active jobs this week that may not complete by the end of the week, you place these on the following week as well. You can then review later, and mark "COMPLETE" at the end of this week, if in fact they didn't need carrying over.
Does that work for you?

[bgoree09]

Good afternoon,

If I interpret correctly it sounds like the easiest thing to do would be to split "completed" into something like a previously completed and a current completed with a total. At the end of the week you could copy the total completed over the previous number and zero all of the current figures (so, on a reset, the previously completed would be a running total of all the completed parts at that time). As for keeping the data within the formula, you could probably do something like that with macro logic, but your cells themselves would have to be static figures (at least as far as I can tell). Alternatively you could take a database approach and have lines for work done each week and pull the totals back into a nice report format using sumifs.

Let me know if I'm off or haven't made any sense.

Best of luck,