Forum Discussion
Timesheet Tracker Help with INDEX/COUNT/SUM functions or alternative
Hi! I have inherited a series of timesheet trackers that I'm hoping to identify a formula or solution to easily quantify the PTO classifications that an individual employee took throughout a year.
I have uploaded https://germane-my.sharepoint.com/:x:/g/personal/tshepard_germane-solutions_com/EauMjoGmdM9BhciEbqCAA3kBKJC6ACbbaZ0ZytqLCAnMRw?e=LYfUlF in question with a small sample of the biweekly timesheets that the previous coordinator used, as well as some screenshots at the very bottom.
The MASTER worksheet is one that I created before I ran into a whole slew of errors ranging from #SPILL to #REF to #ERROR before deciding to take it to the forums.
Ideally, I need a formula that will:
- Match the employee name in D2:D40 of the "MASTER" worksheet with those in B6:B44 in each of the sheets that follow (the scrubbed example has 4, but the full workbook has 26, which aligns with the "Sheet Index" in A2:A27 of the "MASTER" worksheet.
- Count occurrences of each PTO classification (from E1:M1 in the MASTER worksheet) referenced in those sheets so that I can capture how many times each matched employee took "HOL"; "PER"; etc.
- There are some discrepancies in the references that my predecessor noted in the cells, such as "-4" to note a half-day.
Any help/guidance on this would be vastly appreciated as I have 26 worksheets to quantify for the last 3 years for compliance and really trying to avoid doing it manually. I'm also open to changing the format/layout of the "MASTER" worksheet to best approach a solution as well.
Let me know if you have any questions! Many thanks in advance.
Example screenshots:
Master worksheet with the associated "Sheet Index" in A2:A27, employees in D2:D40, and PTO classifications in E1:M1.Sample worksheet example of a biweekly timesheet tracker, in which the employees are now B6:B44 and the reference area for PTO classifications is D6:Q44.Lets try the following formula-
=SUM(--(LET(x,VSTACK('7.15:8.26'!$B$6:$B$44),y,VSTACK('7.15:8.26'!$C$6:$Q$44),FILTER(y,x=$D2))=E$1))
6 Replies
This solution will obtain the totals all in one move.
=LET( stack, HSTACK('7.15:8.26'!B6:Q44), a, TAKE(stack, , 1), pto, DROP(stack, , 1), i, ROWS(residents), j, COLUMNS(classification), Summarize, LAMBDA(r, c, LET( each_resident, INDEX(residents, r), each_class, INDEX(classification, , c), filtered, FILTER(pto, a = each_resident, ""), SUM(N(filtered = each_class)) ) ), MAKEARRAY(i, j, Summarize) )The formula stacks the range in each sheet horizontally, picks out the resident and PTO data, and summarizes with MAKEARRAY.
The solution is often only as 'complex' as the arrangement of the data allows. If the data was all stored on one sheet and vertically arranged, we'd have a straight shot to an elegant solution with PIVOTBY or a pivot table.
Thank you! I will also take a look at this for my own insight/knowledge, but I was trying to avoid VBA scripts since our internal network likes to flag/block any macro-enabled files as potentially malicious.
However, I absolutely agree about wishing the data was all stored in on a single sheet! I was contemplating doing it myself before coming to this forum in hopes of avoiding that.
Lets try the following formula-
=SUM(--(LET(x,VSTACK('7.15:8.26'!$B$6:$B$44),y,VSTACK('7.15:8.26'!$C$6:$Q$44),FILTER(y,x=$D2))=E$1))Thank you for this! It's working like a charm so far, outside of some minor data clean-up I need to address on my end to remove inaccuracies. Really appreciate your help!!
This is definitely a job for Power Query, but it would make it much easier if you adjust the structure of your data a bit. Here are some general steps that you'll need to do, but it will be a little different depending on how you decide to proceed.
- Each time period sheet should have the data in a Table (press Ctrl+T to convert the range into a table).
- In a separate workbook, use Power Query to import from the data entry workbook.
- You can combine the data from all the tables. You'll need to learn some Power Query techniques for cleaning it up, since there are some things you'll want to get rid of, such as the HRS columns and blank columns.
- With the day numbers as column headers, you can select the Employee Name column and choose Unpivot Other Columns. This will give you 3 columns - the employee name, the day number, and the PTO code.
- You'll need to convert the day numbers into the actual dates. You could put actual dates in the column headers, but sometimes it's not easy to work with dates in the headers of Tables in Excel.
Once you get your data into a nice clean format, summarizing it will be easy. And once you have the steps completed in Power Query, it will be easy to repeat or refresh in the future.
Thank you, Steve! I agree and considered Power Query as well, but since I inherited 3 workbooks of 26 worksheets each, some of which the previous users had slight variations to formatting, it felt like it would be more of a headache to navigate. Thankfully, Harun24HR's response came up with a good formula-based solution that seems to be working great; especially once I clean up any discrepancies between the sheets that are affecting it.
