Populate calendar based on multiple values

oddy73

New member
Joined
Feb 25, 2015
Messages
10
Reaction score
0
Points
0
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:

Code:
{=IFERROR(INDEX(hours,SMALL(IF(dateX=E$15,IF(project=$D16,hours)),1)),"")[COLOR=#333333]}[/COLOR]

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.
View attachment Calendar_Vlookup_v0.3.xlsx
 
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)),"")
 
[Solved]

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!!!
 
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!!!
 

Attachments

  • Calendar_Index_0.4.xlsx
    24.4 KB · Views: 38
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))),"")
 
the test could not help me but I found a workaround.
This works also fine for me:
Code:
=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))),"")
 
Back
Top