Jan 13 2021 03:51 AM - last edited on Jan 13 2021 11:38 AM by Dylan Snodgrass
I have attached the excel file in question.
The last sheet is the format I was working towards.
I get a productivity workbook sent to me each week that I would like to drop a template into that can pull the necessary data so I can make graphs and visuals for my employees to keep track of their production.
The workbook has a sheet for each month that contains productivity for each week, monthly total, and YTD displayed in columns. They are measured in 30ish functions that are displayed as rows.
I would like to type someone's name in A1 and return their monthly efficiency % totals into the next 12 columns and the YTD as the 13th.
The formatting our production manager uses is a little funky and i cant control how she does it. So ideally I this template would be plug-and-play.
I am having trouble getting the right combination of lookup , match, index, and offset functions.
Any help is appreciated!
Jan 13 2021 07:15 AM
First, let me advise that you take down your sample spreadsheet, because it contains the names of actual employees and thus violates the terms of this public forum.
Second, having looked at it if only very briefly, I can tell you that you (your company, your department, whatever) are making a common mistake in keeping your records on separate worksheets for each month. That makes it a lot harder for Excel to do what it excels at doing--taking a large set of data and analyzing it. A LOT HARDER.
That final report you're wanting to create would be a piece of cake with a single function (FILTER) if your data were all on a single sheet. I'm going to create an example for you (since I've already opened the file), and will post it back here without the names of real people.
But if you get to this before hand, please take down your original sample file and replace it with one that substitutes the names of Disney characters or Star Wars characters or Jane Austen characters--your choice.
Jan 13 2021 07:37 AM
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.
Jan 14 2021 02:05 AM
@mathetes I appreciate your replies and I apologize for posting against the community guidelines.
Your responses were exactly what I was afraid of....a misuse of Excel.
The production manager's sole job is to work on the sheet. She has been with the company since the dawn of time and, as you clearly point out, uses Excel in the same fashion. The more I dig into this sheet I realize that my best option is to manually extract the data each month into my own sheet. Time consuming...yes, but it will get the end result I desire and plenty of practice with Excel. I was being generous calling it "a little funky" and I really appreciate you explaining everything, it is very validating.
I am hopeful that making my own workbook will demonstrate how we can use the production data (and Excel) more effectively.
Again, thank you very much for your time and knowledge!