+ Reply to Thread
Results 1 to 11 of 11

Need help populating data onto calendar

  1. #1
    Registered User
    Join Date
    08-13-2014
    Location
    Maryland
    MS-Off Ver
    2010
    Posts
    7

    Angry Need help populating data onto calendar

    I have start and end dates, month to month on tabs, need a second table to populate those events into a calendar form, example tab 1 (Aug events) to tab 2 (AUG 2014) calendar, how do i populate into Calendar using the start and end dates?

    Please help!

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

    Re: Need help populating data onto calendar

    It would help if you attached a sample file (the FAQ describes how to).

    Take a look at this thread:

    http://www.excelforum.com/excel-new-...o-a-sheet.html

    which seems to be a similar problem to yours (i.e. activities in a date range, put into a calendar format).

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-13-2014
    Location
    Maryland
    MS-Off Ver
    2010
    Posts
    7

    Re: Need help populating data onto calendar

    Sample Event list.xlsx

    Here you go sir, I just did august, but i need to have it for every month...

    Thank you for your help.

  4. #4
    Registered User
    Join Date
    08-13-2014
    Location
    Maryland
    MS-Off Ver
    2010
    Posts
    7

    Re: Need help populating data onto calendar

    event list Start.xlsx

    Here is what I started from one of the ones I founded on your previous threads. I think I have to much data for this one but i like the set up, that is what I was trying to do. I am ok with it being on one excel sheet. The only issue I have is trying to group the event together to sort of merge the cells or the event be one the same row,also this calendar only has 7 rows.

    I know what I would like to do is complicated, I am just a beginner at excel...

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

    Re: Need help populating data onto calendar

    Quote Originally Posted by jpickle View Post
    ... I think I have to much data for this one ...
    Take a look at the attachment in post #12 of this thread:

    http://www.excelforum.com/excel-gene...-calendar.html

    I set this up to display up to 10 rows per day, but there is a range drop-down which enables you to choose items 1-10, 11-20, 21-30 and so on, so you can accommodate many more items occurring on the same day.

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    08-13-2014
    Location
    Maryland
    MS-Off Ver
    2010
    Posts
    7

    Re: Need help populating data onto calendar

    # 12 Post fits the amount of data needed, but how do I do a start and end date with it showing the event daily until it ends?
    Can you get this much data on this sheet? The same thing with the range?

    event list Start.xlsx

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

    Re: Need help populating data onto calendar

    I've made the changes in the attached file, so you now have another drop-down in column M where you can select the range-number of items.

    It is not a good idea to manually colour the entries in the calendar, because if you change any of the parameters (like the month or year or range-group) then the colours will not change to reflect the new data. It would be better to use conditional formatting to give you the colours.

    Hope this helps.

    Pete
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-13-2014
    Location
    Maryland
    MS-Off Ver
    2010
    Posts
    7

    Re: Need help populating data onto calendar

    Thank you for all your help, the conditional color, yeah I am not sure how to do that...

    I apologize for my limited knowledge in excel, I am a beginner with formulas outside of auto sum.

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

    Re: Need help populating data onto calendar

    Okay, well you had set up colours manually in your example file - blue for "Site Support and Training throughout Cp Pendleton and 29 Palms - I MEF", and orange for both "VALEX" and "Mountain Peak 14-01/MCSIT 3", so I'll take you through what you need to do for those and then you can add some more yourself.

    First of all, we need to remove the manually-added colours and then add 3 conditional formats, but there are already some colours on the sheet, so highlight the block of cells from B3 to H12 (i.e. the first week) and then select No Fill from the Fill Color icon. With that block still selected, click on Conditional Formatting | New Rule | Use a Formula... , and then put this formula in the dialogue box:

    =B3 = "VALEX"

    Click on the Format button and the Fill tab and choose Orange from the palette, then click OK twice to exit the dialogue box. We need to do this twice more with different conditions and colours, so with the cells still selected, click again on Conditional Formatting | New Rule | Use a Formula... , and then put this formula in the dialogue box:

    =ISNUMBER(SEARCH("Site support",B3))

    and click the Format button and choose Blue. Click OK twice. This particular formula will change all the cells that contain "Site support", with or without other text, and is a bit shorter than using the formula:

    =B3 = "Site Support and Training throughout Cp Pendleton and 29 Palms - I MEF"

    which can be used if you wish. For the third condition, ensure the cells B3:H12 are still selected and click again on Conditional Formatting | New Rule | Use a Formula... , and then put this formula in the dialogue box:

    =ISNUMBER(SEARCH("mountain peak",B3))

    Click the Format button and choose Yellow, then OK your way out. Note that SEARCH is not case-sensitive, and that Excel will automatically adjust the cell references to all the other cells that were highlighted. You can now apply the same formats to the other blocks (weeks) of cells by using the Format Painter, i.e. ensure the cells B3:H12 are highlighted, click on the Format Painter icon, then select cells B14:H23, and the same formats will be applied to those cells. Repeat for the other blocks of cells (including those that appear blank at the moment (B58:H67).

    Now if you change any of the parameters in the drop-downs the colours will automatically change. If you want to add any other conditions/colours, just repeat the process, i.e. select the cells in the block B3:H12, click on CF (etc.) and put the appropriate formula in the dialogue box - if it is a simple word/phrase then you can use =B3="word", but if it is longer then you can use the =ISNUMBER(SEARCH(... formula, then choose an appropriate colour. Once you have set all these up in the top range (first week), then use the Format Painter to apply the same formats to the other weeks.

    I've set this up for you in the attached workbook, so that you can extend it further if you need to, but remember that not everyone likes multi-coloured printouts, so be judicious in your use of colours. I've also adjusted the column widths so the calendar looks better when printed out in landscape.

    Hope this helps.

    Pete
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-13-2014
    Location
    Maryland
    MS-Off Ver
    2010
    Posts
    7

    Re: Need help populating data onto calendar

    Thank you so much!!!!

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

    Re: Need help populating data onto calendar

    Glad to be of help.

    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. Auto Populating Calendar for Multiple Dates from Column Data
    By TrackingDates in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-18-2014, 03:39 PM
  2. Replies: 2
    Last Post: 04-28-2014, 05:17 AM
  3. Calendar help populating via worksheet
    By frankly75 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-21-2013, 12:45 PM
  4. Replies: 0
    Last Post: 02-21-2013, 09:04 AM
  5. Populating a calendar
    By Mark D. Haag in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2012, 05:16 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