Monthly Job Resource Assignment Scheduler

[rjatherton]

I am interested in automating an existing Monthly Job Resource Assignment Schedule that is currently manually updated. The current scheduler is created in Excel and provides for a visual presentation of the resources (personnel) that are scheduled to particular jobs for the month on a daily basis. The purpose is to identify who is scheduled and where for the purposes of ensuring all resources have a job and to identify the needs of a particular job, should all resources be assigned.

I am capable of creating a UI for capturing the user entries RESOURCE assignments per JOB and then placing them on the DATA sheet.

A few things that I am attempting to accomplish or that I am struggling with and would appreciate help are:

1. Dynamically generate the information on the calendar sheet based on selected month and year. I found a few examples, so I might be able to accomplish this on my own but I would need some help with the fluctuating number of rows per week.

2. Populate the information from the DATA sheet into the calendar, see attached sample file. Note: each JOB may not have the same RESOURCES every day (row count), as the number of EMPLOYEES assigned to that JOB can fluctuate each day.

3. Color code the JOB cells and RESOURCE assignment cells shown on the calendar. Not sure if Conditional Formatting, Styles, or some other formatting solution is the best option?

4. Most efficient way to update the calendar when information in the DATA sheet is updated. Would you just re-build the entire calendar each time?

5. Populate a list of RESOURCES not assigned to a JOB for each day and list below the jobs, i.e. "NOT ASSIGNED" list.

6. Dynamically create a group that includes the number of rows for each week, so that you can hide week(s).

Any assistance or guidance with one or all of the six items listed above is greatly appreciated!