This is my first post and was hoping someone could help with my dilemma. I stumbled upon the Solver function in my search but I am not familiar with it and have no idea how to implement it. I will try to be as clear as possible.
My client wants my company to use a particular time sheet. I receive total hours of each employee and must break up the hours(into the "Prep" column) worked per work scope("Line #'s), but there are limits.
My client wants my company to use a particular time sheet. I receive total hours of each employee and must break up the hours(into the "Prep" column) worked per work scope("Line #'s), but there are limits.
- Sum of "Actual Applied Hours" to the work scope for each employee must match the employee's worked hours for the day.
- Total of "Actual Applied Hours" must equal Total of "Actual Applied Hours" for the Time sheet.(This shouldn't be a problem if the above code works correctly)
- A certain amount of hours are given to us per work scope. These are more flexible. We can exceed the Planned Hours, but want to stay below if possible or as low as we can if we do(more often than not) exceed. Therefore I would like to place a priority on the work scopes("Line #'s) that have the most hours remaining first. The code should apply a larger value to these lines.
- The crew leader's time should be split up between all lines of his crew using the same rules as above.
Essentially, I need a code to run in the following manner
-Find "Actual Applied Hours" out by subtracting delays from "Total Hours" to set the max allowable range
-Place priority on Lines with the most "Hours remaining". Again, this criteria can be exceeded, but prefer to stay under or as low as possible if exceeded
-Split each employees "Actual Applied Hours" up between each of their Line #'s according to the priority assigned by the code above. Put this number into the "Prep" column
-I will then copy and paste the balanced time sheet to a new workbook for reference.
Can anyone provide any input? I don't even know where to begin with the code. Is this even possible with Excel? This is very time consuming because I have over 200 employees and 30 to 40 Line #'s per time sheet. I must enter this data daily into a progress tracker, fill out time sheets, and report it to my client. The bit of info attached is just an example and can be modified. However, the time sheet cannot be. Any help would be greatly appreciated.
[TABLE="width: 856"]
<colgroup><col><col><col><col><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD="colspan: 2"]Permit validation[/TD]
[TD]0.75[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]
[/TR]
</tbody>[/TABLE]