+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    02-08-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    6

    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
    Attached Files Attached Files
    Last edited by picsou; 06-18-2019 at 11:37 AM.

  2. #2
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    9,258

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

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    9,258

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

    And BTW, acknowledging help improves your chance for further help... (https://www.excelforum.com/excel-pro...ml#post5090729)

  4. #4
    Registered User
    Join Date
    02-08-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    6

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

    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
    Last edited by picsou; 06-18-2019 at 11:33 AM.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,698

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

    Try pasting the following into cell F2 then drag the fill handle over and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    02-08-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    6

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

    Hi,

    I've tried it on my excel file at the office,
    It works perfectly well, many thanks for that, except when I have to different date ranges for one employee.

    If he's off between the 01/04/19 and the 05/04/19, then between the 10/04/19 and the 15/04/19, the cells between 05/04/19 and 10/04/19 are marked with an X.

    Do you have any suggestions please?

    Many thanks

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,698

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

    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.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-08-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    6

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

    Quote Originally Posted by JeteMc View Post
    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.
    that's perfect, thank you very much!

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,698

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1