Populate Calendar (names/Dates) with Names and Dates ranges as data

Good Afternoon,

I have to manage a project and to monitors the employees' holidays for the next 6 months

Here is an example of what i'd like to achieve

I've actually manage to do it with either both a SUMPRODUCT and an INDEX/MATCH function with a similar formula to the one displayed below

but the issue is that it's a realllly, really heavy one, as I have to handle a bigger data range.
I also have some vba skills but the only way i've found to improve it so far is just to copy paste every results as values to avoid it to be that big (the issue is only splited in smaller chunks and it doesn't change that much).


I wondered if you could help me with a more efficient way to do it, please

Thank you very much,
Picsou

Hi Pepe Le Mokko,

Apologies, I thought I had answered a long time ago,
I've uploaded a file too.

Once again sorry,

regards,
Picsou

Try pasting the following into cell F2 then drag the fill handle over and down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

The following works for the scenario described in post #6.
Populate column D using: =IFERROR(IF(INDEX(C$1:C1,AGGREGATE(14,6,ROW(A$1:A1)/(A$1:A1=A2),1))<B2,INDEX(C$1:C1,AGGREGATE(14,6,ROW(A$1:A1)/(A$1:A1=A2),1))+1,""),"")
Populate column E using: =IF(ISNUMBER(D2),B2-1,"")
Apply the following formula as a conditional formatting rule to the range H2:V5 =AND(MINIFS($D$2:$D$13,$A$2:$A$13,$G2)<=H$1,MAXIFS($E$2:$E$13,$A$2:$A$13,$G2)>=H$1)
The font color is set to white if true.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.