multi location text insert/people picker dropdown/matrix selection workload distribution

I know very little about excel but am willing to put in work to figure things out... I just don't know what formulas to start with.

Background: Wife works with 22 other dietitians in a clinical hospital setting, but only four are allowed to take time off at a time. Wife wants to quit her job because the holiday scheduling is so inflexible. I said, "Wait! I can fix this with Excel!" I know what I want it to do but I don't know how to get there yet.

Vision: I want to create a scheduling calendar that automatically reassigns workloads to qualified (in that area) dietitians if available to allow for flex scheduling, e.g. if there's another person that can cover transplant my wife can take her day off. The twist: not all dietitians are equally qualified so I have to track coverage counts to keep the workload evenly distributed (and not punish the most competent folks with the most work).

Where I'm at: I have an input page created with a time off request entry area, list of employees, buttons for "add new employee," "remove employee," "request time off," and "update dietitian qualifications." I have a separate sheet with a qualification matrix (employee name vs qualification with X for qualified and Y for primary qual, aka what would need to be covered if that person took time off). I have another sheet with a horizontal annual calendar where all 23 dietitians are color-coded by department (and thus, qual).

Where I need help:

- I don't know how to create a people-picker dropdown to ensure name input misspellings don't throw off the code
- I don't know how to code my if/then where if an employee requests time off, then excel looks for a replacement from my matrix of qualifications starting with the *next* person (to create some sort of waterfall of extra duty so employee 1 doesn't get saddled with all the extra work) or to create an additional duty tracker that excel uses to assign work to the qualified person with the least amount of add'l duty days
- I don't know how to make excel look at long requests as separate entries broken down by week so one person doesn't get saddled with long absences (like maternity leave)
- I don't know how to convert the days off request input to a horizontal calendar input but I feel confident I can youtube that without help here

*solved*
- I don't know how to create a tool that adds or removes a text line to multiple locations (my "add new employee" button), some as an inserted cell and some as an inserted row.
- I don't know how to make the program ask for inputs in a popup text box for updating quals (a nicety but def not required, I'm planning on just linking the button to a macro to open the right sheet for manual entry
*solved*

So, I'm very much a beginner in excel but I know the program has the capability, especially with formulas and VBA. Please help point me in the right direction for formulas to research and I'll take it from there (unless this has been done before and instead of letting me reinvent the wheel you can drop a link). Thanks!

Dave

It sounds as if you already have some of your 'scheduling calendar' in place. I would suggest uploading what you have, with names changed of course, leaving the formulas/code that is working in place. Highlight and manually fill in the parts of the calendar with which you need help.
To attach a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

Got a bit done before needing to call it an night. Notice that on the 'Vacation Tracker' sheet I moved "Click NEW" outside of the table. With that done producing the following formula to fill the table on the 'Employee Calendar' sheet is easier:

Formula:

Please Login or Register  to view this content.

Notice that the results are either zeros or the vacation codes. I thought that might be more meaningful than all absences being 1's, however if the 0/1 designation is important, simply remove the final argument *Vacations[[Vacation Code]:[Vacation Code]] and re-commit by pressing enter. Also I conditionally formatted those cells containing values other than zero for the purpose of demonstration, I know that this isn't the way you want the final product to appear.
Before I proceed with conditional formatting I need to know if there is a precedence to the 'Letters of XY' table. In the directions it is stated that transplant dietitians' calendars are purple backgrounds. Some dietitians seem to be listed in more than one category, so I would need to know how you want that to be handled before proceeding.
I'll try to get back to this some time tomorrow, if one of the other contributors has not solved the issue beforehand.
Let us know if you have any questions.