Populate calendar based on multiple values

[oddy73]

Hey guys,

I am new to this forum.

Currently I am working on a small project and I am very close to the solution.

The case is as followed:
I have three columns - ABC - DateX, Project and Hours. They get populated via other sheets, the data changes every month. I made also from each column a dynamic ranged.

From column D till column AI i have a calendar.
I use an Index function to get the hours from column C to populate the calendar based on the date and project.

However, the Index function does not work correctly, it shows the wrong values on the calendar and when change a value it changes other values in the calendar.

In another forum someone helped me by providing me the following solution:

{=IFERROR(INDEX(hours,SMALL(IF(dateX=E$15,IF(project=$D16,hours)),1)),"")}

As you can see in the attachment, the first of January it provides the wrong value in the calender.

What do I do wrong?

Thanks in advance!

See attachment for more details. Calendar_Vlookup_v0.3.xlsx

[Bob Phillips]

The problem is that the selection match returns the value

IF(date=E$15,IF(project=$D16),hours))

and then uses this VALUE as a row index. You need to return the row number

=IFERROR(INDEX(hours,SMALL(IF((date=E$15)*(project=$D16),ROW(hours)-1),1)),"")

[oddy73]

The problem is that the selection match returns the value

IF(date=E$15,IF(project=$D16),hours))

and then uses this VALUE as a row index. You need to return the row number

=IFERROR(INDEX(hours,SMALL(IF((date=E$15)*(project=$D16),ROW(hours)-1),1)),"")

Thanks Bob for our quick response!!
I am testing it like crazy and it works perfectly!!!

[oddy73]

Hi Bob,

Thanks again for your help. I really enjoyed developing further the calendar.
During increasing the functionalists of the calendar, i bump up to an unseal behavior.

When I have only one instance in the holiday/sick section, the calendar behaves very strange. The moment I put the second instance, than its all fine.
I looked closely into the code, but did not manage to find the "bug".

Please find attached the file.

Thanks in advance Bob!

Thanks Bob for our quick response!!
I am testing it like crazy and it works perfectly!!!

[Bob Phillips]

When you have just one entry in the absence table, the IF tests within the SMALL function doesn't return an array, just a scalar value, so the function doesn't work correctly.

One way to handle it is to test specifically

=IFERROR(IF(COUNT(hours)=1,IF(AND($A2=J$2,$B2=$I3),$C2,""),INDEX(hours,SMALL(IF((date=J$2)*(project=$I3),ROW(hours)-1),1))),"")

[oddy73]

the test could not help me but I found a workaround.
This works also fine for me:

=SUMIFS($C:$C,$A:$A,J$2,$B:$B,$I3)

Thanks anyway for looking into my request, i am very happy that there are people that take time to help one and other

When you have just one entry in the absence table, the IF tests within the SMALL function doesn't return an array, just a scalar value, so the function doesn't work correctly.

One way to handle it is to test specifically

=IFERROR(IF(COUNT(hours)=1,IF(AND($A2=J$2,$B2=$I3),$C2,""),INDEX(hours,SMALL(IF((date=J$2)*(project=$I3),ROW(hours)-1),1))),"")