+ Reply to Thread
Results 1 to 11 of 11

Help with posting data to Calendar with dropdowns.

  1. #1
    Registered User
    Join Date
    03-21-2019
    Location
    NA
    MS-Off Ver
    2019
    Posts
    6

    Help with posting data to Calendar with dropdowns.

    What i'm looking to accomplish is post events from one sheet to the calendar on the next sheet. Simple enough but instead of a big 12 month calendar for each entity, I'm looking to consolidate it into one Calendar view that anyone on my team can view their specific entity and responsibilities. Attached is a mock up of what i'm working with. Sheet 1 contains the calendar with a drop down for month and property, and sheet 2 contains the list of properties and the corresponding events/dates for the entity.

    Thanks in advance, any questions or anything i can help with please let me know.
    Attached Files Attached Files
    Last edited by bn452211; 04-23-2019 at 10:12 AM.

  2. #2
    Registered User
    Join Date
    03-21-2019
    Location
    NA
    MS-Off Ver
    2019
    Posts
    6

    Re: Help with posting data to Calendar with dropdowns.

    Anyone able to help?

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

    Re: Help with posting data to Calendar with dropdowns.

    Yes, I'll get back to you later on, after I've watched a bit of TV.

    Pete

  4. #4
    Registered User
    Join Date
    03-21-2019
    Location
    NA
    MS-Off Ver
    2019
    Posts
    6

    Re: Help with posting data to Calendar with dropdowns.

    Great, thank you Pete. I look forward to seeing what you come up with. Thank you in advance.

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

    Re: Help with posting data to Calendar with dropdowns.

    I don't understand your data in your Properties sheet. I would expect there to be a date in column F, though you just show a single number. In column G you just show another number - is that meant to be the duration in days that you want the display to be shown?

    Also, I presume you would want the Item column to be displayed on the calendar?

    Pete

  6. #6
    Registered User
    Join Date
    03-21-2019
    Location
    NA
    MS-Off Ver
    2019
    Posts
    6

    Re: Help with posting data to Calendar with dropdowns.

    My apologies for the confusion, i had broken the date up in my attempts to fix the problem. The month is column F and the day is column G. If the formula works better when the entire date in one cell by all means go ahead. And you are correct, i would like the item to be displayed on the calendar.

    Thank you.

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

    Re: Help with posting data to Calendar with dropdowns.

    I'm setting this up so that you can choose the month and year for the calendar to be displayed from two drop-downs. I also have another drop-down where you can select the Property (Alpha, Beta, Gamma etc.), or leave this blank to display all of them. Can you tell me how many different property names are you likely to have? (so I can set up the named range accordingly - well, a bit bigger, to account for more data being added).

    I'm assuming that you will have a start date in column F of your Properties sheet. Does that mean that column G is no longer needed, or could it be used for an end date, if the display is to represent a range of dates for each property? Are your entries only for single days at a time?

    On my calendar sheet I can display up to 10 distinct "events" for each day. Is that sufficient for you, bearing in mind that you can display ALL properties, as well as individual ones? I have the facility (via another drop-down) where you can display up to 10 "events", but you can also select a range of 10, so that you can have 1-10, or 11-20, or 21-30 etc. Do you want to retain this facility?

    I'm basing the calendar on the one I posted in this thread in March:

    https://www.excelforum.com/excel-gen...-calendar.html

    in case you want a preview of what it might look like. Please get back to me with answers to the latest set of questions.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    03-21-2019
    Location
    NA
    MS-Off Ver
    2019
    Posts
    6

    Re: Help with posting data to Calendar with dropdowns.

    The drop downs are key thank you.
    There will be about 50 properties, we have a little less now but just optimistically speaking.
    G is no longer needed if you put the whole date (mm/dd/yyyy) in column F.
    10 items a day should also be sufficient as the most per day now is 4.
    Your audit calendar is ideal, adding the ability to sort by individual property and see events for just that property is key.

    Thank you Pete.

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

    Re: Help with posting data to Calendar with dropdowns.

    See if the attached file does what you are after.

    You can see that I have added a sheet called Names, which has a named range (also called "Names") defined as the range B2:B101 on this sheet, and in this range you can enter your property names - just add any new ones to the bottom of the list.

    In the 'Properties and Items' sheet you can enter your data into columns D to F as required. These can be entered in any order, so there is no need to insert or delete any rows (and that would probably mess up the formulae anyway). I've added some dummy data to test a few things out, so you can just delete that after you've played about with it. There are two columns of formulae in A and B, and these have already been copied down to row 1000, but you can copy these further if you wish. The formula in column A sets up a unique reference depending on the date in column E, and column B controls what is displayed. You can hide columns A to C if you wish, so that the sheet looks just like your data table.

    In the Calendar sheet you can select the month and year of interest using cells K5 and K6, and the display will automatically adjust. In a display that covers the week from Sunday to Saturday, a 30-day month which starts on a Saturday or a 31-day month which starts on either a Friday or a Saturday will need 6 weeks worth of rows to display the complete month by spilling into the Sunday (and possibly the Monday) of the 6th week. In order to reduce the amount of screen space, I have arranged for these "orphan" days to appear on the top row of the calendar, as can be seen for March 2019 and June 2019.

    There is another drop-down in cell K1, and with this you can select the name of the property - doing so will result in only those property details being shown. You can also leave K1 blank, and this will result in all details being displayed. In this case, the display will also include the property name, for clarity.

    Anyway, play about with it, and let me know what you think.

    Hope this helps.

    Pete
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-21-2019
    Location
    NA
    MS-Off Ver
    2019
    Posts
    6

    Re: Help with posting data to Calendar with dropdowns.

    Pete,
    This is perfect. You are an absolute legend. Can you point me in the direction of some material where i can better understand exactly what you did to get these results? I'd like to learn more about excel than the basics i learned during schooling.

    Again thank you so much

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

    Re: Help with posting data to Calendar with dropdowns.

    You're welcome, and I'm glad it works for you.

    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).

    It is difficult to suggest where you might be able to find sites to give you a better understanding of how it works, as there is so much out there on the internet. If you have any specific questions about any of the formulae, then I will try to answer them here, and in fact this site is a great resource for learning more about Excel, so just browse through some of the threads as takes your fancy.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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