About "The Employee:"
First Paragraph:
I am looking at Play Employee.xlsx right now and I don't see any column for any 10 Day status.
I apologize, I meant the 1st response column, in this column the employee will be entering dates, as well as the 2nd response column and the action date column.
The supervisor enters dates on the application date column and the date assigned column.
You said, "
the worker will go to their log and usually just clear the cases for documents and narrate any documents that are still needed." What do you exactly mean by "Their log?" No Document columns are in the Play Employees workbook. By "Narrate" do you mean they are speaking?
Again, I'm sorry I was speaking in terms we use in our business. The employee's log is essentially their person workbook (ex. Play Employee) each employee has their own and we usually refer to them as logs.
Also by narrate I mean that the employee will enter their remarks on the assignment they touch in the comments column, this column will be updated with the employee's comments/remarks, in text form, every time they touch an assignment.
You said, "
select 1st 10 day as well as enter the date in the lst 10 day column." That is two actions and implies that there are some multiple of two more columns, which I don't see.
Correct, it is essentially two actions. If they need to send the 1st request for a response to the client, they will first enter the date which the response by the client is due in the 1st response column which is column K and they will select 1st response or first 10 day in the status's drop down list. If I don't have that list set up in the status column, I apologize, but it is normally there and I can upload an example of what I am talking about in the morning.
The worker will do this series of steps twice, once for the 1st request for a response (column K) and status update (column M) and then repeat the steps when they send the 2nd request for a response (column L) and change the status (column M) to be 2nd response.
Second Paragraph:
"
however an employee must still be able to make any changes to those cells which the assignments are completed due to changes sometimes occurring." Are those changes made by the Supervisor and that assignment resent to the Employee. IOW, Who is the first to know about the changes, the Supervisor or the Employee?
Changes to the status of an assignment are always made by an employee, the employee is usually always the first one to know, sometimes a supervisor may be aware of it, but he or she almost always has the employee process the assignment's new status. Status (column M) values can be unprocessed, (empty,) "Approved," "1st response," "2nd response," and Denied." Changes happen periodically usually through a 45 sometimes 60 day process. That is usually the time frame that is takes for an employee to actually complete the process of an assignment.
Third Paragraph:
"
Every time an employee touches an assignment and does some sort of work on it, they must enter an action date in the action date column" Automatic via VBA code. Also that column changes every day an action is made.
Oh awesome, that'll save time!
In Conclusion:
From what you said, it looks like the only interaction with the UserForm that an Employee needs is to see the Assignments, select one, change the Status, and
select the Zip code and Bank Data. All other functions can be handled by VBA Code.
It should be assumed that ancillary functions like save and get new assignments, etc. are included.
Yes that is essentially all the employees do, they do enter dates in columns (K and L) however we can just make the an automatic thing as well. Any time the employee changes the status to 1st response it means that 10 days from that action the client's documents are due. Same thing for the 2nd response. So either the employee can enter that date or it can auto populate in those columns.
About "The Supervisor:"
Second Paragraph:
Good to know about that. We will need to encode cut and paste abilities or some other method to transfer that data.
Third Paragraph:
We can add to that UserForm a section that displays that information for the particular Employee that the Supervisor is working on at the moment.
Fourth Paragraph:
"
but I would like a tracking tool that can also do it by application month's, if that makes sense." All Employees by month or just a monthly summary, ie, one line per month for many months. Or Both. In each case it just means one more sheet in the Master log and a little bit of code.
Oh no that is not what I meant at all. Okay so Employees get assignments every month (usually corresponds to the app date in column D). The supervisors would like the be able to track the progress of the assignments of every employee by the month of applications, (Column D.) For example in the Play Master log, the first tab has a tracking tool that tracks all the approvals, denials, unprocessed, 1st responses, and so forth for all employees. What would be great is to have a tracking tool just like that but tracks the status by the application month. Hopefully that makes more sense.
Another Issue Paragraph:
The functionality of all formulas will be moved to the VBA code
You are starting to have to keep track of a lot of data. If we can keep all the functionality we have discussed, would you mind if we very slightly change the way in which that data is organized?
What I am suggesting is that all Employee's data is permanently stored in an Employee book like the current Play Employee.xlsx.
Employees come and go, quite often in different Fiscal periods. A sheet for them would be created in Master Employee Log.xlsx book when they start, and would be removed at the end of the fiscal period that they leave. We would still save all data every fiscal period.
Master Log.xlsx would only have the Master Log sheet, the Reference sheet, and the sheets mentioned in About Supervisors, para 4 above.
I believe this would make the code easier to write and to maintain.
I think I see what you're saying and it looks like this would make things much easier. Keep in mind the master workbook does need the tracking tool to be able to monitor the progress of the cases, like the one in the Play Master.
In regards to my post #14
We really do need to know the
exact order in which they perform every step in the process.
I really wish that there was an exact order but in all honesty every employee works differently. The steps that I mentioned above are the steps that every employee takes but it may not be in that order. For example an employee may just approve an assignment right away and not need to do the 1st and 2nd response steps. But they all touch the same columns.
Also, how do the employees know which documents are cleared and how they know which ones are still needed?
The employees know the business process and eligibility for their assignments. We have other programs where they can see those documents. We don't need to incorporate any of those programs for these workbooks. They are managed by another agency.
How do the Supervisors receive new assignments?
They get them from a different department and program there is also no way to incorporate that process in these workbooks, at least not right now.
While creating this system will probably take less time than I have already spent on it, (around 14 hours, so far,) nobody want us to deliver it and then discover that it is not right and we have to do it all over again.