+ Reply to Thread
Results 1 to 16 of 16

Populate calendar in excel from list, help!

  1. #1
    Registered User
    Join Date
    01-22-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    8

    Populate calendar in excel from list, help!

    Hi, I'm brand new and have basically no knowledge of excel (haven't worked with it much since high school)...

    I'm trying to build a calendar that can be automatically updated from a list of birthdays, anniversaries, meetings, etc. It's for a community calendar that will be printed and distributed around the area and people send in their birthdays, etc to be listed on the dates in the calendar.

    So I was ideally thinking a list could be made with names and dates and then somehow it magically be inputted to a template of some kind (in excel or another program like coreldraw?).

    Is there something out there that exists already that can do that or does anyone know how to build one that has some instructions?

    Thank you so much for your time!!!

    Lexie

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Populate calendar in excel from list, help!

    Hi
    Do you have a mock of such calendar , and some examples of your names and dates?

    See at the top of the page instructions of how to upload your excel sheet.

  3. #3
    Registered User
    Join Date
    01-22-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    8

    Re: Populate calendar in excel from list, help!

    Hi Belinda, thanks for reaching out!
    No I don't have any mock ups. I've never done this before and I don't really have any knowledge of excel. I'm basically starting from scratch.

    I was probably just going to use a calendar template in excel and then based on some other research, was going to create another file with the list divided into headers labeled, date, birthdays, anniversaries, meetings, etc.

    Is it possible to start with a basic list like that and then find a formula that will input the appropriate event onto the calendar date?

    Thanks again!

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Populate calendar in excel from list, help!

    Here is a calendar mock-up
    Please add a list of events for example - and advise how you would like them to be populated in your calendar...you can add manually to understand where you want your formulas..
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-22-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    8

    Re: Populate calendar in excel from list, help!

    Hi Belinda,
    I typed in a few examples on how it would ideally look in the calendar along with a list that would hold the initial information to act as the database.
    Hopefully it gets attached properly.

    Thanks!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Populate calendar in excel from list, help!

    Hi,

    Please find attached :
    A11 =IFERROR(INDEX(Sheet1!$B$1:$B$10,SMALL(IF(A10=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)),1)),"")
    B11 =IFERROR(INDEX(Sheet1!$B$1:$B$10,SMALL(IF(A10=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)),2)),"")

    Copy every 2 columns to the next date.

    Let me know if that works for you.
    Attached Files Attached Files
    Last edited by Limor_OP; 01-24-2021 at 11:03 AM.

  7. #7
    Registered User
    Join Date
    01-22-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    8

    Re: Populate calendar in excel from list, help!

    Hi Belinda,
    I'm not 100% sure what you mean, I'm to copy the two formulas you typed and paste them on every square with in each date box? Like I said, pretty much zero experience with this.
    Also, the type of occasion wouldn't need to be automated. I just need a whole bunch of names and meetings placed in their corresponding dates. And more often than not, there will be many entries per date. The type of occasion heading was more for organization.

    Thanks!

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Populate calendar in excel from list, help!

    Hi,
    Didnt understand from your response if the format I uploaded fits you or not....
    If you need more than 2 events/names in one day - you will need to adjust the calendar somehow to be able to incorporate all the entries cause each entry needs a separate cell....

  9. #9
    Registered User
    Join Date
    01-22-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    8

    Re: Populate calendar in excel from list, help!

    K, I formatted about half of the calendar (aesthetically, not with any functions or formulas)
    to sustain more than one entry per date. The occasion will have a slightly different format
    than the 'meeting' entries. I included another list to help demonstrate how ideally the information
    could be inputted. So the only columns that contains information that needs to go on the calendar itself,
    would now be columns C & D. Hope this makes a little more sense.

    Thanks for your time!
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Populate calendar in excel from list, help!

    Take into account that you changed the calendar so that if you update the month in E2 the calendar wont be automatically updated, and will remain on January ....is that what you want?

  11. #11
    Registered User
    Join Date
    01-22-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    8

    Re: Populate calendar in excel from list, help!

    I started a new calendar and vaguely formatted it to have up to eight
    entries per date and then I made a couple copies and changed the months
    and that seems to work for the calendar to automatically update the dates.
    I included the same list as before to hopefully get added to the new calendar
    design. Does this help?
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Populate calendar in excel from list, help!

    Hi
    I added formulas instead of you manual records.
    Please find attached.

    S26 =IFERROR(INDEX(Sheet1!$C$1:$C$12,SMALL(IF(S$19=Sheet1!$A$2:$A$12,ROW(Sheet1!$A$2:$A$12)),1)),"")
    S27 =IFERROR(INDEX(Sheet1!$C$1:$C$12,SMALL(IF(S$19=Sheet1!$A$2:$A$12,ROW(Sheet1!$A$2:$A$12)),2)),"")

    A29 =IFERROR(INDEX(Sheet1!$D$1:$D$12,SMALL(IF((A$28=Sheet1!$A$1:$A$12)*(Sheet1!$D$1:$D$12<>""),ROW(Sheet1!$A$1:$A$12)),1)),"")

    A35 =IFERROR(INDEX(Sheet1!$C$1:$C$12,SMALL(IF((A$28=Sheet1!$A$1:$A$12)*(Sheet1!$C$1:$C$12<>""),ROW(Sheet1!$A$1:$A$12)),1)),"")
    A36 =IFERROR(INDEX(Sheet1!$C$1:$C$12,SMALL(IF((A$28=Sheet1!$A$1:$A$12)*(Sheet1!$C$1:$C$12<>""),ROW(Sheet1!$A$1:$A$12)),2)),"")
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    01-22-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    8

    Re: Populate calendar in excel from list, help!

    Where do I put these formulas? And do I have to customize them depending on what cell they get assigned to?
    Like I said, I have basically zero experience with this program when it comes to functions.
    Can you provide some step by step instructions on how to use these? I fiddled around with it to try and figure it out, but
    I can't seem to get it to work.
    Also, do these formulas include the possibility of hundreds of names?

    Thanks again!!

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Populate calendar in excel from list, help!

    Here is modification of the file that is attached to post #12.
    The following formula is placed into each date cell on the "1" sheet: =IFERROR(INDEX(Sheet1!$C$1:$C$12,AGGREGATE(15,6,ROW(Sheet1!$A$1:$A$12)/(A$10=Sheet1!$A$1:$A$12),ROWS($A$1:$A1))),"")
    The formula references the entries in column C on Sheet1.
    To change the month from the drop down in cell R30.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  15. #15
    Registered User
    Join Date
    01-22-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    8

    Re: Populate calendar in excel from list, help!

    Hi there,
    Thanks so much for your template, it work's great for the most part but I've been experimenting with it to see how it works and I can't get anything to push to the calendar after
    about 12 rows on Sheet1. Does the formula need to be manipulated to include a certain amount of rows?
    Also, is there any way for the entries to list from the bottom of the date square and work their way up? So for example, on the Feb. 1st square, 'use new calendar' would show up on the 18th row and the next entry for Feb. 1st would go on the 17th row, etc. Hope that makes sense.
    Thanks!

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Populate calendar in excel from list, help!

    I believe that this modified formula does what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I have applied the formula to the file attached to this post.
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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. Calendar wont populate from list
    By Helpppp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-17-2020, 08:30 PM
  2. [SOLVED] Auto-populate excel calendar from excel list 2018
    By J.W.Excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-03-2018, 08:47 PM
  3. Auto populate events to excel calendar from a list
    By lridley2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2016, 05:22 PM
  4. Populate a Calendar using from a list of activities
    By laura13 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-21-2013, 11:57 AM
  5. [SOLVED] Shade Cells and populate list in Excel calendar
    By staysecure in forum Excel General
    Replies: 7
    Last Post: 01-04-2013, 11:07 PM
  6. Populate Calendar with List of Dates
    By akmiao in forum Excel General
    Replies: 15
    Last Post: 09-30-2011, 01:38 PM

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