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!!
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!!