+ Reply to Thread
Results 1 to 11 of 11

Auto populate calendar from list of events in 2 tables

  1. #1
    Registered User
    Join Date
    08-01-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Auto populate calendar from list of events in 2 tables

    Hi all,

    I'm a newbie to excel and I can't seem to find a solution to my problems - would greatly appreciate if anyone can provide solutions to them!

    I have 2 questions:

    1. On one sheet, I have a table of planned events with the title (column A) and the date (column B) and day (column C). For the same event, I have 2 dates (column B + C, and column D + E). I would like to create an automated table on another sheet that rearranges the information on the aforementioned table in chronological order. Is there a way to do this?

    2. From the list of events, I need to auto populate a monthly calendar. For this, I've tried using the attachment in post #4 in the following thread: http://www.excelforum.com/excel-new-...readsheet.html but it seems the calendar is only applicable till 2015.

    Thanks in advance for any help!
    Last edited by blndsjy; 08-01-2016 at 10:03 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,940

    Re: Auto populate calendar from list of events in 2 tables

    In the attachment you referred to , change the Data Validation list in K4 of "Calendar" to the years you require.

  3. #3
    Registered User
    Join Date
    08-01-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Re: Auto populate calendar from list of events in 2 tables

    Hi, thanks for the help but I've tried it to this message:

    "The value you entered is not valid. A user has restricted values that can be entered into this cell."

    Any ideas how to resolve that?

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

    Re: Auto populate calendar from list of events in 2 tables

    You need to select cell K4, then click on Data | Data Validation | Data Validation (yes, twice), and then you will see the list of years separated by a comma. Add other years to the end of the list, with a comma between them, and you can remove earlier years from the list if you no longer need them - Data Validation drop-downs only show 8 items on screen, so by removing the earlier years you won't have to scroll down so much.

    It would help to understand your 2-table query if you attached a sample Excel workbook. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Pete

  5. #5
    Registered User
    Join Date
    08-01-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Re: Auto populate calendar from list of events in 2 tables

    Hi Pete,

    Thanks a lot for the help!

    I've attached the sample with this post. Sheet 1 contains information that I will input manually. Sheet 2 is the table I would like to auto populate.
    Attached Files Attached Files

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

    Re: Auto populate calendar from list of events in 2 tables

    You don't really have to specify the day in columns C or E in the first sheet, as this can be done with a formula, e.g. with this in C8:

    =IF(B8="","",TEXT(B8,"ddd"))

    and this in E8:

    =IF(D8="","",TEXT(D8,"ddd"))

    Copy those two formulae down as far as you think you might need.

    As for the other sheet, is there any particular order that you want the items from Sheet1 to appear in? If you want to retain the colouring for English and Chinese, it might be better to have another column for "E" or "C", so that you can easily see where the data has come from. Are you likely to want to enter a range of dates in Sheet1 (i.e. from start_date to end_date), or is a single day's entry for each event sufficient?

    Pete

  7. #7
    Registered User
    Join Date
    08-01-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Re: Auto populate calendar from list of events in 2 tables

    I've been using the =(TEXT, "ddd") formula to specify the day! I believe it's the same?

    As for the other sheet, is there any particular order that you want the items from Sheet1 to appear in?
    I would like the information in sheet 2 to be arranged in chronological order, just so I can have a better idea of what events are coming up next.
    Are you likely to want to enter a range of dates in Sheet1 (i.e. from start_date to end_date), or is a single day's entry for each event sufficient?
    A single day's entry is sufficient.

    Regarding the calendar in #4, is there a way that the events' title in the calendar view retain the colours used in the sheet it extracted the information from?

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

    Re: Auto populate calendar from list of events in 2 tables

    Okay, I'll set this up for you later - I'm working on a file for another poster at the moment, so I'll get back to this calendar file later on.

    Pete

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

    Re: Auto populate calendar from list of events in 2 tables

    I've done the necessary modifications to the calendar file, as attached.

    In the Data_Entry sheet you can add the details of your events/classes in any order in columns A, B and D, and the formulae in columns C, E and F will automatically fill in. You will notice that I've added a ranking column in F, which is used on the Activities sheet to derive the combined table in chronological order by column B. The formulae have been copied down to row 100 (as can be seen by the hyphens), and you can copy down further as required.

    The Activities sheet combines the two tables into one, as requested, and shows the entries in chronological order by column B of the Data_Entry sheet. This is fully automatic, so you shouldn't need to do anything on this sheet, other than copy the formulae down below row 100 if required. Note that I have added Conditional Formatting to the table, to give you the colours that you used in your sample file.

    The Calendar sheet enables you to choose the month and year of interest using the drop-downs in K3 and K4, and I have changed the year ranges in K4 as discussed earlier. I've also applied conditional formatting on this sheet to give you the colours for the two languages. I've actually amended all the formulae in this sheet, which means that it is no longer compatible with early versions of Excel, but as your version of Excel is 2013, that shouldn't be a problem.

    Hope this helps.

    Pete
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-01-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Re: Auto populate calendar from list of events in 2 tables

    Hi Pete,

    Thank you, that was exactly what I wanted! Appreciate the help!

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

    Re: Auto populate calendar from list of events in 2 tables

    You're welcome.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. populate events calendar from inputs
    By excelforum2k16 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-31-2016, 03:44 AM
  2. 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
  3. [SOLVED] Auto Populate a Linear Calendar Based On A List With Date Ranges
    By falafelologist in forum Excel General
    Replies: 3
    Last Post: 05-20-2015, 08:15 PM
  4. Populate an Excel calendar template with events from a separate table
    By Dana_Carter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-12-2014, 12:14 PM
  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. Need help with auto-populate list schedule details to calendar, please.
    By aghamilton327 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-19-2014, 03:20 PM
  7. [SOLVED] Auto-populate calendar using data list
    By myexcelquestions in forum Excel General
    Replies: 1
    Last Post: 06-13-2012, 08:58 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