help needed with scheduling autofill with multiple options from one spreadsheet to another

Hello,

I'm running into trouble formulating my Daily Schedule for work. I'm not sure if what I want can even be done.

There is a "Schedule" file attached that shows all of the different tiers of employees I have. Each tier would have the names of the staff which I replaced with random names for the purpose of uploading. In the same row as their name it will display their schedule for the month under each dates column. "E" for Early Shift, "L" for Late Shift, and "D/O" for day off. Occasionally I will have a "V" for Vacation and "PD" for Personal Day or "R" for Request. For the purpose of this those last three should be counted as day off. Any other occurrences in place of these are rare and do not need to be accounted for.

Separately I have a file "Daily Schedule" which needs to manually be filled in by referencing the "Schedule" file. (If I need to combine both files into one spreadsheet to accomplish what I need that is no problem)

What I am trying to do is find a formula (or any method) that will search the date column in each tier of employee. So for example I would like it to search each date column under the 'Design Associates' section in "Schedule" for "E" and if "E" is found place all of he employees with an "E" next to their name under the 'Early Shift' section of the "Daily Schedule" file. I would then like it to place all those with "L" in the 'Late Shift' Section. Then I need it to do the same for all of the other tiers. But for the other tiers I just need the formula to place any employee working (therefore any employee with "E" of "L") into their appropriate section of the "Daily Schedule" file. Lastly I would like it to find ALL employees with a "D/O", "V", and "PD" and place them into the 'Day Off' Section of the "Daily Schedule" file.

That is the primary focus I have. In addition to that if you could help with some smaller items in these files.

In "Daily Schedule" you will notice there are 7 Days to be filled in. If possible can I apply the above solution to fill in all 7 days accordingly?

Can the dates in the "Daily Schedule" be set to update if I adjusted the first one to a different week?

Instead of the dates in the "Daily Schedule" updating when I change one. Can I set them to match the date from the column they are pulling the "E", "L" and "D/O" data from?

If someone has a "PD" or "V" can I make it so that it will put their names in the day off section AND mark (PD) OR (v) next to their name?

For certain groups of employees can the formula be set so that if their scheduled shift is "E" it places them into their slot in the "Daily Schedule" file showing their shift and name. For example if on the "Schedule" file the manager 'Amy' had an "E" shift it would place her into the 'Manager' section of the "Daily Schedule" file like so [E - Amy]. And then add the "L" managers after in the same manner.


I realize this is quite a lot to ask. So I appreciate any and all help in this matter! Any little bit helps! I've been struggling writing and writing VLOOKUP, LOOKUP, MATCH, IF, and countless other formulas trying to accomplish any of this. Thank you in advance to anyone that can help!