Cumulative total in single cell

Hi,
I have been struggling with an issue for about one week, I'm reaching out to others to find a solution. I am trying to link data from one worksheet to another and create a cumulative total in a single cell. Cell F4 represents the progress of a particular job on my master production schedule (ie: if item X has run 40,000 units out of 100,000 scheduled, F4 will read 40,000). Currently, F4 is being updated manually, each shift adding their respective production to its total. To eliminate math errors and redundancy, I would like F4 to update automatically via inputs from each shift's production report located in another workbook (titled WEEKLY SUMMARYY). Due to production variables, item X is not always listed in the same cell from shift to shift on the individual production reports. Sometimes it is not listed at all. In order to skirt around this and still get the result that I need, I used the following formula in F4 (Shortened to show only one shift, the actual formula is a sum of 21 shifts)

[=(IF(COUNTIF('[WEEKLY SUMMARYY (9) (2).xls]Monday'!$C$10:$C$37,C4)>0,INDEX('[WEEKLY SUMMARYY (9) (2).xls]Monday'!$K$10:$K$37,MATCH(C4,'[WEEKLY SUMMARYY (9) (2).xls]Monday'!$C$10:$C$37,0)),0)+IF(COUNTIF................]

Where C4 is the cell that lists item X, C10:C37 is all of the items that ran for that particular shift, and K10:K37 is the respective production quantities for each item. C4 is located on the master schedule, C10:C37 and K10:K37 are located on the production report.

The above works beautifully, with only one issue. On Monday morning the production reports are wiped clean, thereby making the total in F4 zero. I need help maintaining the total in F4 while eliminating the previous week's quantities in the production reports. I have searched through forums and found others solved this issue using VBA. However all examples were for cells with manual inputs, I attempted to replicate for my cell. All was well when I tested using manual inputs, but once I added the formula I received no results. I have tried everything that I can think of: Creating a circular reference to F4; Making F4 an IF function based on day of week and time of day in order to give myself a window to clear the production report...... Nothing is working out. Sorry for the book that I have written above, any help that could be offered would be appreciated.

Thanks,

Josh

You are correct, the data is cleared manually on Mondays. I have considered a process similar to what you have laid out in option 1. I am holding on to that as a last resort, however, as I would like to eliminate as many manual operations as possible. Option #2 is more what I am looking for, but I don't really know how to write the code to create this shortcut. My excel knowledge is more or less self taught and I have never encountered the need for VBA (or perhaps my ignorance toward the usefulness of VBA has led me to work without it??) Can you help with the code please?

OK.... Here is the code to clear out the variables (shortened to show clearing out Monday only)

Please Login or Register  to view this content.

Accumulator cells will go in column R, starting at R4.
Heading out for the day, thanks for helping me get started with this. I'll check back tomorrow.

OK, that works well within a single workbook, but the accumulator cell and the data to be cleared are located in different workbooks. I tweaked the code as follows and received these messages after an unsuccessful test:

Run-time error '1004'
Method 'Range' of object'_Global' failed

Please Login or Register  to view this content.

Perfect! Still needs a little bit of tweaking to get it to do exactly what I want, but I can handle it from here. Thank you so much for your help!