+ Reply to Thread
Results 1 to 11 of 11

Auto-populating rolling month calendar using drop down lists

  1. #1
    Registered User
    Join Date
    04-30-2019
    Location
    Devon
    MS-Off Ver
    2016
    Posts
    15

    Auto-populating rolling month calendar using drop down lists

    Hi All,

    I'm trying to make a calendar that starts from a month selected from a drop down list and then populates the correct months and dates into the calendar. It has to show the 12 months from the month selected, so it will usually roll over years (i.e. dec 2020 to jan 2021).

    So far I have tables in the DATA sheet for the drop down lists in cells F1, N1, X1 and V3 although I don't think I have the cells in the tables formatted correctly, at the moment the Months table has the "custom formatting" of mmmm (for the full month name).
    Cell B5 (year) is populated from cell F1
    Cell B9 (first month) is populated from cell N1
    The combination of B5 and B9 should then determine what dates are entered into the months (e.g 1st Month = September, Year = 2020, cell C11 starts at 1st, D11 is 2nd, E11 is 3rd etc etc)

    I also then have:
    1. a field in cell F3 in which to enter an MOT date which should apply conditional formatting to that date.
    2. a field in cell N3 to enter a service date, which should apply conditional formatting to that date and then every other date going forward determined by the frequency in months from the drop down selection in cell V3.
    3. a field in cell V1 to enter an inspection date, which should apply conditional formatting to that date and every date going forward determined by the frequency in weeks from the drop down selection in cell X1.

    I'm assuming some or all of this is going to have to be achieved with VBA and I'm fairly comfortable entering VBA in the editor, but I'm new to VBA and don't quite know where to start.
    If anyone could help bit by bit with this or point me in the right direction, I'd be massively grateful..

    Work in progress sheet attached.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,774

    Re: Auto-populating rolling month calendar using drop down lists

    You could do this with a few formulae, rather than VBA. Do you want me to set it up for you (as it will be a bit awkward to describe all the different formulae)?

    Pete

  3. #3
    Registered User
    Join Date
    04-30-2019
    Location
    Devon
    MS-Off Ver
    2016
    Posts
    15

    Re: Auto-populating rolling month calendar using drop down lists

    Hi Pete, thanks for looking at this. If formulae are the way to go that would be great, I've got a bit more knowledge of formulae than VBA (and apparently I'm a poet and don't know it haha).
    Any help would be much appreciated, thanks.

  4. #4
    Registered User
    Join Date
    04-30-2019
    Location
    Devon
    MS-Off Ver
    2016
    Posts
    15

    Re: Auto-populating rolling month calendar using drop down lists

    Hi Pete, hope you're keeping well.
    I was just wondering if you have had any time to look at this?

    I have so far managed to get the months to populate in B9, J9, R9 etc by changing the table for the "Select Month" drop down to month numbers (i.e Jan =1 etc etc) and then using the following formula in B9 =TEXT(N1*28,"mmmm") and then to populate the following month cell J9 with the formula =TEXT((N1+1)*28,"mmmm"), then the next month cell R9 =TEXT((N1+2)*28,"mmmm") etc etc.
    I get the obvious problem though when the month reaches December that the next month cell doesn't roll over to Jan, I'm sure I can figure it out but would I be right that using "TEXT" would cause a problem later down the line when I need to use the month and the year selected to auto populate the individual dates? (e.g cell D11 for June 2020 would be 1, E11 would be 2, F11 3 etc etc).

    If you haven't had time then absolutely no worries buddy. Any explanation on how you'd approach it would be hugely appreciated though

    I've uploaded the workbook with what I've done if that helps.

    Thanks again for looking at this.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,774

    Re: Auto-populating rolling month calendar using drop down lists

    I have spent a bit of time looking at this, and got the first month working no problem.

    One issue I found is that you are using 5 weeks to represent each month, but if you have a 30-day month starting on a Sunday, or a 31-day month starting on either a Saturday or Sunday (with your weekly layout starting on Mondays), this means that one or two days at the end of the month will spill over onto a sixth week. I have tried to overcome this by showing those "orphan" days on the top line of the month (as obviously Monday and Tuesday would otherwise be blank), as many printed calendars are depicted. I might show these in a different colour for clarity using conditional formatting.

    This means though that you can't just pick up a new month from where the last month finished, as that could be on the first line, or indeed on the 4th line (for a non-leap February which starts on a Monday), or the 5th line.

    I've overcome that by using WEEKDAY of the MAX of the previous month to determine where the next month should start, but the formula that I had did not work for Mondays.

    Another approach would be to use 6 weeks for each month, but then you still have the problem of determining where the last month ended.

    I used MATCH on your list of month names to determine the month number, but I'll take a look at your latest file to see what you have done.

    I have left off it for a few days while I did some other jobs in the house and garden, but I'll pick it up again now that you have reminded me (and because it is raining !!).

    Pete

  6. #6
    Registered User
    Join Date
    04-30-2019
    Location
    Devon
    MS-Off Ver
    2016
    Posts
    15

    Re: Auto-populating rolling month calendar using drop down lists

    Thanks Pete, you're a star. Rain has hit us pretty bad down in Devon, think I'm going to need a canoe to get from the office to my car!!

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,774

    Re: Auto-populating rolling month calendar using drop down lists

    I've got the yearly calendar more-or-less working now - just a few problems with the final 3 months.

    In your first post you don't describe in detail what conditional formatting you are looking for. Do you want to change the foreground colour or the background colour (or both) ? What colours do you have in mind ?

    Just to recap, so I don't have to keep scrolling up to the top, you want the MOT date to be affected once, but the service date should also include following dates which are at multiple-monthly intervals depending on V3, and with inspection date the intervals are in weeks? Does a month mean 4 weeks (so always on the same day), or a calendar month (so later dates could fall on weekends) ?

    I'll try and sort out the problems with the calendar later on, after some TV.

    Pete

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,774

    Re: Auto-populating rolling month calendar using drop down lists

    The calendar is working fine now. Just waiting for your feedback on the conditional formatting questions.

    Pete

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,774

    Re: Auto-populating rolling month calendar using drop down lists

    Ah well, I went ahead and used my own colour scheme, and you can see the finished file attached.

    I've put a data validation drop-down in cell N1, so you can use that to choose your month rather than type it in.

    As you want to be able to select the starting month for the calendar, so it will span different years. To avoid confusion, I've shown the month and year in the header blocks for each month.

    As reported above, any "orphan" days at the end of 5 weeks will be shown in Monday/Tuesday on the top row for the month, and will be highlighted for clarity. This affects 2 or 3 months in each year.

    The colours for the dates in F3, N3 and V1 act as a key for the corresponding CF in the main calendar.

    The calendar generates successive dates in Excel terminology, and is formatted to show only days (month and year in the header blocks).

    Hope this helps.

    Pete
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-30-2019
    Location
    Devon
    MS-Off Ver
    2016
    Posts
    15

    Re: Auto-populating rolling month calendar using drop down lists

    Hi Pete,
    That's brilliant, thank you very much. Sorry for the late reply, long weekend with the mountainbike took me away from the connected world for a while. The calendar does everything brilliantly and what you've done with the "orphan days" is great, conditional formatting colours too.
    As soon as I get an opportunity to pay your kindness forward, I'll be helping many Grannies across the road.
    Thank you again Pete.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,774

    Re: Auto-populating rolling month calendar using drop down lists

    I'm glad it meets your requirements. I had fun (!!) trying different things out in developing it. If you need any explanations for the formulae, then feel free to post back.

    Thanks for the rep. 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.

    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. [SOLVED] Counting Sick Days using rolling 12 month calendar
    By bowdropper in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-14-2020, 09:56 PM
  2. Auto Populating calendar that changes all data with each month.
    By VMurtsell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-19-2017, 12:15 PM
  3. Replies: 3
    Last Post: 07-14-2014, 01:02 PM
  4. Rolling 12 month calendar
    By davereggi in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-13-2014, 12:02 AM
  5. 6 month rolling attendance calendar
    By mdobbins in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-23-2013, 04:12 PM
  6. Rolling 3 Month Calendar
    By supachoc in forum Excel General
    Replies: 3
    Last Post: 10-18-2010, 04:25 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