+ Reply to Thread
Results 1 to 15 of 15

Help linking spreadsheet to calendar

  1. #1
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    00000
    Posts
    5

    Help linking spreadsheet to calendar

    Hello,

    I need an Excel spreadsheet by month that will pull data from a spreadsheet that has activity start dates and end dates. I was able to find a thread with a great spreadsheet to do this, yet it only displays one date/day. for example, the activity needs to be shown on the calendar on 3 days if it started on 1/01/2020 and finished on 01/03/2020.

    I have attached the spreadsheet I found and would like to credit Pete_UK for his answer on a post I cannot link since I am new.

    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
    Office 2019 (still learning)
    Posts
    24,604

    Re: Help linking spreadsheet to calendar

    That's an old one of mine - I have subsequently produced others which respond to date ranges (i.e. start date and end date). Perhaps you can attach your own activity file, so I can see how your data is laid out and merge it with an appropriate calendar file.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    00000
    Posts
    5

    Re: Help linking spreadsheet to calendar

    Hi Pete,

    thank you so much for the prompt response.I have attached the master schedule that I am working with. I was able to use your older calendar to show the start dates but I am really looking forward to your help! Thank you in advanced!
    Attached Files Attached Files

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

    Re: Help linking spreadsheet to calendar

    It's a bit late for me to work on it now, so I'll pick it up again tomorrow. Just a few questions in the meantime:

    Is there some reason for your list of events not starting until row 17?

    Do you only want to display the Reviews column (B), or might you want the Task Owner's name (column E) as well?

    My approach for date ranges is to automatically expand the activity records so that there is one per day. This involves inserting a few more columns, which I will do on the left-hand side (and which can be hidden), so your table of activities will look the same as it is now.

    On this early calendar up to 7 events can be displayed for each day, but I have changed that in later calendars - is this still sufficient for you, or would you prefer up to 10 (say) ? On some calendars I have increased this further, by having another drop-down where you can select the range in groups of 10, i.e. 1-10, 11-20, 21-30 etc. Is this something that you may be interested in?

    In later versions I also have the facility to display "orphan" days on the top rows of the calendar. These occur when a 30-day month starts on a Saturday or when a 31-day month starts on either a Friday or a Saturday, and leads to one or two days being displayed on a sixth week (e.g. for August 2020). Instead of this happening, I can display them automatically in the first week, thus enabling more events per day without destroying the look of the calendar.

    Anyway, I'll post back to you tomorrow.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    00000
    Posts
    5

    Re: Help linking spreadsheet to calendar

    Hi Pete, thank you once again for your help. You have no idea how grateful I am! I have been puzzled for quite some time trying to accomplish everything you just mentioned and did not think it could be possible. I really appreciate it .

    Quote Originally Posted by Pete_UK View Post
    Is there some reason for your list of events not starting until row 17?
    I had some instructions for users before. The space is not needed since I will be the only one updating the information.


    Quote Originally Posted by Pete_UK View Post
    Do you only want to display the Reviews column (B), or might you want the Task Owner's name (column E) as well?
    It would be perfect if we could display both

    Quote Originally Posted by Pete_UK View Post
    On this early calendar up to 7 events can be displayed for each day, but I have changed that in later calendars - is this still sufficient for you, or would you prefer up to 10 (say) ?
    It would be great if it were 10!

    Quote Originally Posted by Pete_UK View Post
    On some calendars I have increased this further, by having another drop-down where you can select the range in groups of 10, i.e. 1-10, 11-20, 21-30 etc. Is this something that you may be interested in?
    This would also be great since it will include reviews from three different groups (SUD, MH, and IDD). I can later adjust each group by range.

    Quote Originally Posted by Pete_UK View Post
    In later versions I also have the facility to display "orphan" days on the top rows of the calendar. These occur when a 30-day month starts on a Saturday or when a 31-day month starts on either a Friday or a Saturday, and leads to one or two days being displayed on a sixth week (e.g. for August 2020). Instead of this happening, I can display them automatically in the first week, thus enabling more events per day without destroying the look of the calendar.
    Whichever way you suggest would be amazing. One again, I am extremely grateful for all your help!

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

    Re: Help linking spreadsheet to calendar

    I've got this more-or-less working now, but you mentioned being able to select from 3 groups (SUD, MH and IDD). I can set this up for you, but there is no evidence of these groups in the file you attached to Post #3 - can you attach another file showing where these will occur?

    Cheers,

    Pete

  7. #7
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    00000
    Posts
    5
    Quote Originally Posted by Pete_UK View Post
    I've got this more-or-less working now, but you mentioned being able to select from 3 groups (SUD, MH and IDD). I can set this up for you, but there is no evidence of these groups in the file you attached to Post #3 - can you attach another file showing where these will occur?

    Cheers,

    Pete
    Hi Pete, I was thinking of adding a column which indicated which group the review belonged to. I would upload a file with an example but I’m doing a training today and have no access to my computer. Thank you once more!

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

    Re: Help linking spreadsheet to calendar

    I've attached the file with all the facilities that I described in Post #4.

    In the Task Monitoring sheet you will notice a number of columns (A to G) with formulae in, and then your main data table exists in columns I to N. The formulae have been copied down to row 1000, but you can copy them down further if you wish (and then you could hide those columns to prevent accidental changes). I could describe what each formula does, but basically they expand the date ranges in your table of data so that you have one entry for each date, and it is columns F and G in particular which control what is displayed on the calendar and where. The 130+ rows of your data have been expanded to nearly 470 rows, and this is why you may need to copy the formulae down further (e.g. to row 5000 or 10000, or beyond) if you intend to add more data. There is no need to delete any data - you can just keep adding data in any order at the bottom of the main table, and so you can retain historical information if you need to get at it.

    I have inserted a new column in your table (J) which I have labelled as Group, and arbitrarily filled it with the codes that you mentioned. I've left some blank (mainly in September to November 2019 so you can see what effect this has.

    On the Calendar sheet you can select the month and year of interest using the drop-downs in cells K5 and K6. Up to 10 events can be displayed for each day, but if you have more than 10 events you can see these by using the drop-down in cell K10, whereby you can select the display in groups of 10. Another drop-down in cell K15 enables you to select by group - either SUD, MH or IDD, or All of them. You will notice that the display is made up of the Reviews, Group and Task Owner from your main data table, and if any of these are missing then it is fairly obvious due to the hyphens between each of these items. Note that if a Group is missing for a particular record, then this will be shown for any of the selected Groups in K15.

    You will notice also that I have incorporated "orphan" days in either Sunday or Monday of the first week (e.g. for May 2020 or August 2020), so the calendar will only ever need 5 weeks to display the full month. Any orphan days are shown with a different colour. The full monthly calendar can be printed out on a single sheet of paper if you need a hard copy.

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

    Hope this helps.

    Pete
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    00000
    Posts
    5

    Thumbs up Re: Help linking spreadsheet to calendar

    Hi Pete,

    You are a genius and a lifesaver and I'm really grateful for developing this calendar for me. It is extremely helpful to have someone with your experience in this forum!
    I am grateful for all your help. If you're ever in Austin TX or need anything from here let me know!

    Thanks!

    -Aldo

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

    Re: Help linking spreadsheet to calendar

    Thanks for the kind words, Aldo, and for marking the thread as Solved and for the rep (I usually have to remind most users about these).

    I've glad to help, and hope you enjoy using the calendar file.

    Pete

  11. #11
    Registered User
    Join Date
    06-03-2020
    Location
    USA
    MS-Off Ver
    18.2004.1162.0
    Posts
    3

    Re: Help linking spreadsheet to calendar

    how can i unprotect the sheet to change the months? what is the password to do so?

    thank you

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

    Re: Help linking spreadsheet to calendar

    The months and years are selected via data validation drop-downs (I presume it is the years that you want to change), so select the cell then click on Data Validation and then make the appropriate changes, i.e. a comma-separated list.

    Hope this helps.

    Pete

  13. #13
    Registered User
    Join Date
    06-03-2020
    Location
    USA
    MS-Off Ver
    18.2004.1162.0
    Posts
    3

    Re: Help linking spreadsheet to calendar

    Thank you. When i click on the cell i get "... To make a change, unprotect the sheet. You might be required to enter a password."

    When i click FILE, INFO, and then try to unprotect it a password prompt appears.

    I can not make any changes, see any months, or see other years.

    thank you so much for your help!

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

    Re: Help linking spreadsheet to calendar

    I've just downloaded the file from Post #8. There is no protection applied to the file, so I do not know why it is asking you for a password.

    Pete

  15. #15
    Registered User
    Join Date
    06-03-2020
    Location
    USA
    MS-Off Ver
    18.2004.1162.0
    Posts
    3

    Re: Help linking spreadsheet to calendar

    not sure why, but its working now. Weird. Thank you so much for the help!!!!! You are the best!!

+ 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. Help linking calendar to spreadsheet
    By kathy4484 in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 05-14-2012, 02:13 PM
  2. Linking Excel Calendar to Spreadsheet
    By msher64 in forum Excel General
    Replies: 2
    Last Post: 02-13-2009, 11:32 PM
  3. Excel should allow calendar based spreadsheet linking.
    By kacivic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 01:05 AM
  4. Excel should allow calendar based spreadsheet linking.
    By kacivic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2005, 11:05 PM
  5. Excel should allow calendar based spreadsheet linking.
    By kacivic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2005, 10:05 PM
  6. Excel should allow calendar based spreadsheet linking.
    By kacivic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-04-2005, 09:05 AM

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