+ Reply to Thread
Results 1 to 15 of 15

Calendar with auto-populating events (multiple events in a single date)

  1. #1
    Registered User
    Join Date
    02-18-2021
    Location
    Basel
    MS-Off Ver
    365
    Posts
    7

    Calendar with auto-populating events (multiple events in a single date)

    Dear all,
    I am trying to generate a calendar which should automatically add events matching a specific date.
    Since a picture is worth a thousand words, here is an attachment.
    Any words of wisdom?
    Thanks in advance for your help!calendar.JPG

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calendar with auto-populating events (multiple events in a single date)

    Quote Originally Posted by zankzank View Post
    Dear all,
    I am trying to generate a calendar which should automatically add events matching a specific date.
    Since a picture is worth a thousand words, here is an attachment.
    Any words of wisdom?
    Thanks in advance for your help!Attachment 719581
    Not generally the case with assistance here with Excel. Quite the reverse in fact. A workbook is worth a thousand pictures.
    You have a workbook, just upload it. Not many of want to spend time recreating something you already have. We always like to test answers for obvious reasons so don't expect us to jump through hoops.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    02-18-2021
    Location
    Basel
    MS-Off Ver
    365
    Posts
    7

    Re: Calendar with auto-populating events (multiple events in a single date)

    I admit I completely misunderstood the purpose of this forum then, as I came over here for help.
    The picture that I attached is a snapshot of a workbook that I created manually, simulating the desired outcome.
    I have spent countless hours trying to figure it out, mixing and matching Vlookup, MATCH and other functions, and I failed miserably.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,438

    Re: Calendar with auto-populating events (multiple events in a single date)

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook (we can't do anything with a picture of one).

    NOTE: you will get amazing help here, but if you complain each time you are asked for specific information, you will get nowhere fast. If you expect us to make an effort for you for FREE, then the least you can do is make an effort as requested. It's up to you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Registered User
    Join Date
    02-18-2021
    Location
    Basel
    MS-Off Ver
    365
    Posts
    7

    Re: Calendar with auto-populating events (multiple events in a single date)

    My apologies: it was never my intention to "complain", just like it definitely was never my intention to have somebody else do my work for free.
    I have limited Excel experience (I have a degree in Chemistry), and I was hoping that somebody could recommend a process (e.g. combining different functions). Finally, I certainly don't mind attaching a sample WB.
    Any piece of advice would help.
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calendar with auto-populating events (multiple events in a single date)

    Hi,

    A few points of clarification

    1. How representative is the data in the C3:E5 range?
    i.e. are all the dates in each of the X,Y,Z columns always chronological?

    2. Are all dates in any column ALWAYS later than a date in any of the previous columns?

    3. Is the C3:E5 fixed. This is clearly a representative example and may not represent the production workbook. Do you have more columns and/or rows and does whatever you have vary from time to time.

    It may be possible to create a compound formula to return the results, alternatively and if it were me I'd use a macro. Is that an acceptable solution?

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,143

    Re: Calendar with auto-populating events (multiple events in a single date)

    Given that your upper table will probably grow as you add more data to it, I would suggest that you have that in a separate sheet from the calendar.

    You can generate the dates quite easily, rather than type them in yourself, by putting this formula in cell C8:

    =B8+1

    and then copying this across to AF8. Then you need to decide if you want the dates to continue further across that row, or to have separate rows for each month. If the latter, then you can have this formula (in say B11):

    =AF8+1

    and in C11:

    =B11+1

    copied across to AC11, as there are always at least 28 days in February. To accommodate leap years, you can use this formula in AD11:

    =IF(MONTH(AC11)=MONTH(AC11+1),AC11+1,"")

    and then for the start of March, as you don't know exactly where February ends, you can have this formula in B14:

    =MAX(11:11)+1

    The rest of the calendar can then be built up by adding 1 onto the previous date. Everything is now controlled by the start date in B8 - change this to 1st January of another year and the rest of the calendar will follow automatically.

    Hope this helps.

    Pete

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calendar with auto-populating events (multiple events in a single date)

    Mmm,

    Pete's post makes me question what is meant by a 'calendar' in this context

    I'd misinterpreted the request as a need to generate the row 9 values which appeared to be derived from the matrix.
    Maybe zankzank will clarify

  9. #9
    Registered User
    Join Date
    02-18-2021
    Location
    Basel
    MS-Off Ver
    365
    Posts
    7

    Re: Calendar with auto-populating events (multiple events in a single date)

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    A few points of clarification

    1. How representative is the data in the C3:E5 range?
    i.e. are all the dates in each of the X,Y,Z columns always chronological?

    2. Are all dates in any column ALWAYS later than a date in any of the previous columns?

    3. Is the C3:E5 fixed. This is clearly a representative example and may not represent the production workbook. Do you have more columns and/or rows and does whatever you have vary from time to time.

    It may be possible to create a compound formula to return the results, alternatively and if it were me I'd use a macro. Is that an acceptable solution?
    Dear Richard,
    thank you so much for your follow-up.
    I will try to answer your questions point-by-point:
    1. All the dates in each of the X,Y,Z columns will always be dd.mm.yy
    2. Unfortunately, to make things more complicated, the dates in one column will not always be later than a date in any of the previous columns.
    3. The range is fixed, but there will be many more columns (e.g. Tests) and Rows (e.g. Projects).

    Sadly, my experience with MACROs is very limited, but I am trying to learn
    Thanks again for your help!
    ZZ

  10. #10
    Registered User
    Join Date
    02-18-2021
    Location
    Basel
    MS-Off Ver
    365
    Posts
    7

    Re: Calendar with auto-populating events (multiple events in a single date)

    Quote Originally Posted by Pete_UK View Post
    Given that your upper table will probably grow as you add more data to it, I would suggest that you have that in a separate sheet from the calendar...
    Dear Pete,
    Thank you so much for your message.
    I want to apologize for the confusion: I wasn't really trying to figure out how to create a calendar, but rather how to auto-fill it with events matching specific dates (contained in a table/range).
    I totally agree with you though: I should definitely place the calendar (however it is generated) in a separate sheet!
    Thank you again for your help!
    ZZ
    Last edited by AliGW; 02-19-2021 at 10:43 AM. Reason: PLEASE don't quote unnecessarily!

  11. #11
    Registered User
    Join Date
    02-18-2021
    Location
    Basel
    MS-Off Ver
    365
    Posts
    7

    Re: Calendar with auto-populating events (multiple events in a single date)

    Dear Richard,
    My apologies for the confusion: English is not my first language and I am sorry if sometimes I can't explain things clearly.
    You are absolutely correct: my goal here is to find a combination of functions to generate the row 9 values which are derived from the matrix.
    Thanks again for your help,
    ZZ

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,143

    Re: Calendar with auto-populating events (multiple events in a single date)

    It would help if you explained what the letters A, B, C and X, Y, Z will actually represent in reality. If these are actually words (names of Projects and Tests), then I don't think it would look very good to join these together in one cell separated by a comma. It would be better to list them in their own cell underneath the appropriate date, so that you might have, say, five cells under each date so that you can accommodate up to 5 "events" for each day.

    Obviously, the row for February dates (and for subsequent months) would be moved down to accommodate these 5 cells.

    I think your upper table would be better laid out if you listed each date vertically, with an appropriate entry for Project and Test in other columns. That would make it much easier to auto-populate the Projects and Tests on the corresponding date.

    I have submitted many such calendar files to the forum and my general approach is to have a list of events in one sheet and the calendar in another sheet. The calendar generally displays a single month, where the month and year can be selected via drop-downs. Up to 10 events can be displayed for each day. I can amend one of these files to suit your requirements if you like.

    Hope this helps.

    Pete

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calendar with auto-populating events (multiple events in a single date)

    OK I'll put a macro version together for you.

  14. #14
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calendar with auto-populating events (multiple events in a single date)

    Hi,

    See attached.

    It contains two range names and the following macro


    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    02-18-2021
    Location
    Basel
    MS-Off Ver
    365
    Posts
    7

    Re: Calendar with auto-populating events (multiple events in a single date)

    Dear Richard,
    thank you so much for the Marco: it works perfectly!
    I will try to understand the code so that I can apply it to the real dataset.
    I can't thank you enough for taking the time to look into it!
    I wish you a wonderful weekend!
    Warmly,
    ZZ

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Multiple events same date calendar will only show one event per day not all
    By KtB73 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-19-2020, 12:34 AM
  2. How to auto populate events into calendar
    By madsjohnson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-11-2019, 12:33 PM
  3. Replies: 1
    Last Post: 08-04-2016, 01:33 AM
  4. View multiple events in a date calendar
    By imzhakmaya in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-21-2014, 06:40 AM
  5. Auto populate events in calendar on userform
    By imzhakmaya in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2014, 07:31 AM
  6. Populating an Excel Calendar to Reflect Ongoing and Future Events
    By Lruegg in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-30-2012, 07:20 PM
  7. Populating a calendar with list of events
    By bwells3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2011, 05:31 PM

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