Hi there
Has been a while since I posted here, and most probably someone else asked the very same question, however could not find exactly what I was looking for. Hope everyone is safe during these crazy times.
I have been building an attendance tracker for my employees (200+ employees to be exact) and my monthly dashboard looks quite busy and not the way I would like it to look.
Instead of having 200+ employees showing on my dashboard, and most of them having no leave taken whatsoever within the particular month I have chosen, I would like to filter down and only show those employees who have any leave booked in within the chosen month.
So, here we go, I have an input table with 3 columns, column B being my names field, column C leave start date and column D leave end date.
My filter filed looks like this. Cell K would be the month chosen, then as a helper cell K first day of the chosen month and cell M last day of chosen month.
I have used the below formula to filter through the names column, nd it works, however if I have one employee whose leave is peeking through another month, that filed won't be shown within my result list (result column is K).
INDEX($B$3:$B$10, SMALL(IF($C$3:$C$10>=$L$3, IF($C$3:$C$10<=$M$3, ROW($C$3:$C$10)-ROW($C$3)+1)), ROWS(K$6:K6)))
Example:
As you can see in the second image, my name isn't picked up within FEBURARY.
Any help would be appreciated.
Many thanks in advance.
Sabi
Has been a while since I posted here, and most probably someone else asked the very same question, however could not find exactly what I was looking for. Hope everyone is safe during these crazy times.
I have been building an attendance tracker for my employees (200+ employees to be exact) and my monthly dashboard looks quite busy and not the way I would like it to look.
Instead of having 200+ employees showing on my dashboard, and most of them having no leave taken whatsoever within the particular month I have chosen, I would like to filter down and only show those employees who have any leave booked in within the chosen month.
So, here we go, I have an input table with 3 columns, column B being my names field, column C leave start date and column D leave end date.
My filter filed looks like this. Cell K would be the month chosen, then as a helper cell K first day of the chosen month and cell M last day of chosen month.
I have used the below formula to filter through the names column, nd it works, however if I have one employee whose leave is peeking through another month, that filed won't be shown within my result list (result column is K).
INDEX($B$3:$B$10, SMALL(IF($C$3:$C$10>=$L$3, IF($C$3:$C$10<=$M$3, ROW($C$3:$C$10)-ROW($C$3)+1)), ROWS(K$6:K6)))
Example:
As you can see in the second image, my name isn't picked up within FEBURARY.
Any help would be appreciated.
Many thanks in advance.
Sabi