Productivity Workbooks
The request came down to me one day from the Director of the Counseling Center. She wanted the front office manager and myself to re-design therapist productivity sheets that would automatically link to and chart contact hours and goals and eliminate the need for duplicate data entry. She set a drop dead date of 3 days.
I also needed to scaffold relatively inexperienced excel users who had been forced to fly blind without a net on a previous tool - meaning I needed to document and explain formulas, write clear step by step instructions, design tables so that the user didn't go outside ranges of defined data, and in general make everything relatively flexible and forgiving.
Such a seemingly simple request - and how it made me bang my head against my desk time after time, until I discovered the secret of dynamic look-up and linking. By simply having the user enter an individual's name, these formulas would look up and pull in the productivitry information on that individual no matter where the user put them in the table and no matter how the user sorted them.
Summary tables linked to monthly sheets within the same workbook, and charts "ran" off the data in the summary tables. The summary tables not only gave individual productivity but also monthly and year to date productivity for the entire program. Additionally an external workbook with a sheet for each individual linked to the data in the summary tables.
Monthly Group Productivity Sheet
Monthly Individual Productivity Sheet
After cycling through a year with the newly designed Productivity Workbook for each of our counseling programs, the executive director of the agency started asking for variations on the design. Specifically she wanted one for the case management programs as well - the same, but yet different. For case management she wanted to be able to set one monthly goal for each case manager across all the programs they worked in. She wanted their contact hours and productivity calcuated biweekly instead of by month.
By this time I had learned a few new tricks with excel. This time I didn’t
set up a different workbook for each program with the 12 tabs for each
month. Instead, within the new workbook I set up one goals sheet and then
a worksheet for each program with 26 columns for the biweekly
breakout of contact hours. The summary data sheet design stayed the same
but now looked up and summed across programs –giving total
contact hours, goals, and productivity for each case manager. The charts
essentially remained the same.
Case Manager Productivity Sheet
As the workbook passed through the hands of multiple users, I did find that I needed to make the lookups more forgiving, so I developed formulas to filter out commas, spaces, and periods before the lookups were performed.
Sometime later, the executive director asked for a report that summarized productivity data from both the Case Manager Productivity Sheet and the Therapist Productivity Sheet. So I came up with yet a third workbook for her that compiled and summarized productivity for both groups. By that time, the organization had put the same employee in charge of updating both the Therapists and the Productivity workbooks. It soon became evident to me that we needed to arrive at a common design between the two, keep both workbooks in the same folder (a socially negotiated issue between the two departments), and use Access to live link to the workbooks and summarize the data for the executive director. This arrangement also had the advantage of being easily archived at year-end and greatly simplified the formulas, thus simplifying maintainability. This time, borrowing what I learned from working on the Creative Curriculum project, I created a tab for each employee (called one-stops in the case manager workbook, and daily activity logs in the therapist workbook) that listed all the programs and time intervals for each individual. In the end users seemed to find this much more intuitive.
Case Manager Productivity Sheet
Access Reporting System (since this is live-linked to the workbooks, you will need to to use the Linked Table Manager under Tools/Database Utilities on the Access Menu Bar to update the links after you download everything.)
Access Documentation (zipped)
I also created data access pages for the director - so that all she needed to do was go to the folder and double-click on her reports, which opened up in Internet Explorer. This was preferable to using an Access Menu system, which presents as another level of complexity. However, at this point, users do need to copy and paste the Access Summary Report into an excel workbook to make the combined chart.