I have a roster of employees and the dates they have requested off on a sheet named Test. The employee name is in column A, their location is in column B and their approved time off will either be in C through E (PTO), F through J (PH) or K through P (DH) depending on the entitlement time used. On sheet 2 i have a calendar that i would like this information displayed on. The year is in A1, the month (spelled out) is in A2 and the day is in C4. I am looking for a formula that will provide the employee name, location and type of entitlement used (PTO, PH or DH) for that date. This information would be displayed in cells A5 through A9 depending on how many employees have been approved for the same date. The current formula will not correctly display the entitlement actually used.
Test worksheet:
View attachment 109382
Sheet 2:
View attachment 109383
Here is the formula i currently have, which i am not able to get to indicate the correct entitlement. It just goes with the first item found when pulled down.
Current formula:
=IFERROR(INDEX(Test!$A$3:$A$45,SMALL(IF(DATE($A$1,MONTH(DATEVALUE($A$2&"1")),$C$4)=Test!$C$3:$P$45,ROW(Test!$C$3:$P$45)-ROW(Test!$A$3)+1),ROW(1:1)))&" - "&INDEX(Test!$B$3:$B$45,SMALL(IF(DATE($A$1,MONTH(DATEVALUE($A$2&"1")),$C$4)=Test!$C$3:$P$45,ROW(Test!$C$3:$P$45)-ROW(Test!$B$3)+1),ROW(1:1)))&" "&IF(AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)<4,"(PTO)",IF(AND(AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)>3,AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)<9),"(PH)","(DH)")),"")
I appreciate any help that can be offered.
Test worksheet:
View attachment 109382
Sheet 2:
View attachment 109383
Here is the formula i currently have, which i am not able to get to indicate the correct entitlement. It just goes with the first item found when pulled down.
Current formula:
=IFERROR(INDEX(Test!$A$3:$A$45,SMALL(IF(DATE($A$1,MONTH(DATEVALUE($A$2&"1")),$C$4)=Test!$C$3:$P$45,ROW(Test!$C$3:$P$45)-ROW(Test!$A$3)+1),ROW(1:1)))&" - "&INDEX(Test!$B$3:$B$45,SMALL(IF(DATE($A$1,MONTH(DATEVALUE($A$2&"1")),$C$4)=Test!$C$3:$P$45,ROW(Test!$C$3:$P$45)-ROW(Test!$B$3)+1),ROW(1:1)))&" "&IF(AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)<4,"(PTO)",IF(AND(AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)>3,AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)<9),"(PH)","(DH)")),"")
I appreciate any help that can be offered.
