Help with productivity worksheet

Schmittj 

On second thought, and having thought about your statement here,

The formatting our production manager uses is a little funky and i cant control how she does it.

if indeed you're speaking there about how the raw data are collected, then there's little that can be done until you get that production manager to change it.

It's not just "a little funky."

I'm going to speak very strongly here: the way the raw data are collected is a total mis-use of Excel. It shows every symptom of having been begun in ancient history (like the 1960s) before PCs entered the scene, when records like this were kept on weekly ledgers papers. Then when PCs, and Lotus 1-2-3, followed by Excel, came along people saw those nice rows and columns and transferred their ledger sheets over to "computerized ledger sheets."

But they didn't re-think the whole process of data collection (input) and reporting (output) to take advantage of the truly remarkable tools they now had at their disposal. Instead, they still kept every row of that ledger sheet intact--even though for any one employee they only used 4 or 5 of the 25 rows in each weekly sheet for that employee (your spreadsheet has more wasted space than it has space containing data).

It would take a major reorganization of that data--not hard to do, just time consuming--to get ALL of the useful data (I'd eliminate the blank rows, since they're essentially non-data to begin with) into a single worksheet, with a week date, an employee name, and then the corresponding data for the rows where there is info, ALL of that for all employees and all weeks into a single sheet.

You could then create a dashboard VERY SIMPLY that would show you all kinds of summary data--by employee, by month, by function--whatever.

But unless you can get that reorganization done, I think (and I'm sorry to say it) you're stuck with not only a funky way of collecting data, but sadly never more than an unstable and shaky way to summarize.