+ Reply to Thread
Results 1 to 16 of 16

Data Tab autopopulate in Calendar Tab

  1. #1
    Registered User
    Join Date
    04-14-2021
    Location
    England
    MS-Off Ver
    Office 2010
    Posts
    7

    Wink Data Tab autopopulate in Calendar Tab

    Good afternoon all!

    I would like to keep a spreadsheet with upcoming events (date, time, location, point of contact 1, point of contact 2, event location) and have them populate into a calendar in another tab (time, point of contact 1, location) on the appropriate date. I have seen a few threads with calendar options but none of them seem to be this simple. Could anyone help by sharing formulas/samples to create this type of spreadsheet?

  2. #2
    Registered User
    Join Date
    04-14-2021
    Location
    England
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Data Tab autopopulate in Calendar Tab

    Edited to add an attachment, areas that I would like on the calendar are in yellow on the first tab.
    Attached Files Attached Files

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,935

    Re: Data Tab autopopulate in Calendar Tab

    There's nothing on the calendar tab.

    Please give us some idea about what you want and how it should look. A polar bear in a snow storm doesn't do that.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    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.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  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,548

    Re: Data Tab autopopulate in Calendar Tab

    Are you likely to have events which last for more than a single day (i.e. so that you have a date range, from and to) ?

    Are you likely to have more than one event on any single day? If so, what is the maximum number of simultaneous events?

    Pete

  5. #5
    Registered User
    Join Date
    04-14-2021
    Location
    England
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Data Tab autopopulate in Calendar Tab

    Thank you for the feedback! I added the calendar to give an idea of what I was thinking.

    We are going to have multiple events per day, but no event will exceed a day in length. The maximum number of events per day will likely be 5.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,935

    Re: Data Tab autopopulate in Calendar Tab

    So is the idea that the calendar sheet will automatically change month when we get into May and so on?

  7. #7
    Registered User
    Join Date
    04-14-2021
    Location
    England
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Data Tab autopopulate in Calendar Tab

    You know, it would actually be helpful to have the months in separate tabs. Thank you for helping me think this out! I really appreciate it.
    Attached Files Attached Files

  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,548

    Re: Data Tab autopopulate in Calendar Tab

    Do you really need twelve sheets, one for each month? You could easily have a drop-down which allows you to choose the month (and another one to choose the year), then it will be more universal.

    Also, you have not shown how you would like the data to be displayed on the calendar at the appropriate date. I would assume that you would want the time, then the ref-number and squadron followed by the contact, but please confirm this layout. Note also, that the more info that you want to be displayed, the wider the display will have to become.

    Hope this helps.

    Pete

  9. #9
    Registered User
    Join Date
    04-14-2021
    Location
    England
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Data Tab autopopulate in Calendar Tab

    That would be awesome to do a drop down menu too. I am not great with Excel, which is why I am here for help! The only thing I knew how to do was create a calendar in 12 different tabs.

    The data that I would like to populate on the calendar is the ref number, squadron, and time. The contract/vendor info would be a bonus if that would fit on the line.

    I would be fine with either format, the most pressing issue is how to get the data from the data tab to a calendar tab. Is that a possibility?

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

    Re: Data Tab autopopulate in Calendar Tab

    Okay, I'll get back to you after the football, then I'm away for a long weekend tomorrow.

    Pete

  11. #11
    Registered User
    Join Date
    04-14-2021
    Location
    England
    MS-Off Ver
    Office 2010
    Posts
    7
    You are truly amazing! Thank you so much!

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

    Re: Data Tab autopopulate in Calendar Tab

    I didn't get chance to work on this last night, but I've put something together for you before I go away.

    The attached file has two sheets - one to record the data for your events, and another to show the calendar. In the events sheet I have formulae in columns A and B , and these columns can be hidden to prevent accidental changes. Note that column B controls what is being displayed, so it is quite easy to change this to add more fields if you wish. I've put some test data in the table, which you can delete.

    In the calendar sheet you can select which month and year to display using the yellow cells (K5 and K6), and that month's calendar will immediately be displayed. I've allowed up to 10 events per day, and have colour-coded these with alternate colours for clarity. The display covers 5 weeks - any orphan days will be displayed on the top row (see January 2021 for an example of this). I've noticed that some of the conditional formatting is not working correctly (controlling the lines around the boxes), but I don't have time to correct these now, as I'll have to do some packing.

    Hope this helps.

    Pete
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    04-14-2021
    Location
    England
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Data Tab autopopulate in Calendar Tab

    The spreadsheet is fantastic. It is exactly what we needed! I appreciate all of your help. Have a great afternoon!

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

    Re: Data Tab autopopulate in Calendar Tab

    Glad to be able to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the 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

  15. #15
    Registered User
    Join Date
    09-01-2021
    Location
    Portugal
    MS-Off Ver
    Professional Plus 2019
    Posts
    6

    Re: Data Tab autopopulate in Calendar Tab

    i agree with tory, this is genius work right here, i spent whole day just trying to comprehend the formulas and im loving it, thank you so much Pete!

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

    Re: Data Tab autopopulate in Calendar Tab

    Glad you found it useful, and thanks for the rep.

    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. Autopopulate Events into Calendar
    By kebushong in forum Excel General
    Replies: 0
    Last Post: 08-31-2020, 12:34 PM
  2. Autopopulate Calendar with color and multiple sheets
    By Hudson222 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-28-2019, 09:18 PM
  3. Autopopulate calendar from data
    By bryandgutridge in forum Excel General
    Replies: 5
    Last Post: 01-14-2019, 08:29 PM
  4. Autopopulate dates from table to monthly calendar
    By emjean11 in forum Excel General
    Replies: 4
    Last Post: 10-25-2017, 10:08 AM
  5. Autopopulate (horizontally) cells in calendar via UserForm
    By john55 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-30-2016, 05:02 AM
  6. [SOLVED] Autopopulate Excel Calendar with Data Ranges
    By hlmmc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-12-2016, 07:50 PM
  7. [SOLVED] How to autopopulate calendar with data from different sheet
    By marcella needs help in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-20-2016, 06:07 AM

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