Results 1 to 6 of 6

Thread: Populate calendar based on multiple values

  1. #1

    Populate calendar based on multiple values



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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)),"")}
    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

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,834
    Articles
    0
    Excel Version
    O365
    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)),"")

  3. #3

    [Solved]

    Quote Originally Posted by Bob Phillips View Post
    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!!!

  4. #4
    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!

    Quote Originally Posted by oddy73 View Post
    Thanks Bob for our quick response!!
    I am testing it like crazy and it works perfectly!!!
    Attached Files Attached Files

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,834
    Articles
    0
    Excel Version
    O365
    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))),"")

  6. #6
    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

    Quote Originally Posted by Bob Phillips View Post
    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))),"")

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •