In calendar format on Sheet 1, I have people listed within each day for who is out of the office and for how many hours. I update Sheet 2 daily with a report received indicating name, person's manager, date off, hours off. I am hoping to have the calendar on Sheet 1 update when I apply a filter to the data on Sheet 2. The purpose is for managers to be able to filter down to only see people off from their team.
The formula used to pull the data from Sheet 2 into the calendar on Sheet 1 is =IFERROR(INDEX('PTO Data'!$B:$B, SMALL(IF(ISNUMBER(MATCH('PTO Data'!$D:$D, $D$8, 0)), MATCH(ROW('PTO Data'!$D:$D), ROW('PTO Data'!$D:$D)), ""), ROWS($A$2:A2))),"") for the name. Then =IFERROR(INDEX('PTO Data'!$E:$E, SMALL(IF(ISNUMBER(MATCH('PTO Data'!$D:$D, $D$8, 0)), MATCH(ROW('PTO Data'!$D:$D), ROW('PTO Data'!$D:$D)), ""), ROWS($A$2:$A2))),"") for the hours.
Thank you in advance!
The formula used to pull the data from Sheet 2 into the calendar on Sheet 1 is =IFERROR(INDEX('PTO Data'!$B:$B, SMALL(IF(ISNUMBER(MATCH('PTO Data'!$D:$D, $D$8, 0)), MATCH(ROW('PTO Data'!$D:$D), ROW('PTO Data'!$D:$D)), ""), ROWS($A$2:A2))),"") for the name. Then =IFERROR(INDEX('PTO Data'!$E:$E, SMALL(IF(ISNUMBER(MATCH('PTO Data'!$D:$D, $D$8, 0)), MATCH(ROW('PTO Data'!$D:$D), ROW('PTO Data'!$D:$D)), ""), ROWS($A$2:$A2))),"") for the hours.
Thank you in advance!
