Tracking employee based on leave dates

Hello all,

I am working on a spreadsheet for my company. I have a control sheet where I can input and update all the data of any employee, including when there is leave request.

I have programmed in a way when I select the employee from the drop-down it populates the cells in my control sheet.

A new requirement has been brought up. I need to code the spreadsheet to analyze if the employee is at work or off at any given time.
The data of the employee is spread out in four different sheets, when selected, it copies and pastes where I need it to. The leave is booked in a separate sheet. That was done because an employee might add more than one leave at the time. For instance, they might have requested 3 days off next week, and another 4 days in the first week of April.

For instance, if I select Joe Smith today, and he went on vacation yesterday, when his name is uploaded it should show a code like "OFF." Likewise, if they are at work, it would show a code like "P." But that would be completely contingent on the data found on the vacation track.

Is that possible? If so, how would I go about doing that?


Thanks.