+ Reply to Thread
Results 1 to 46 of 46

Formatting Data of an Auto Populating Calendar Workbook

  1. #1
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Question Formatting Data of an Auto Populating Calendar Workbook

    So, from this forum (thank you!), I have a dynamic calendar on one worksheet that populates data from another worksheet. This is exactly what I was looking for, but I need to edit the workbook to fit my needs.

    The attached files are the production schedule ("Metal Production") that I am creating, and the aforementioned calendar workbook ("Demo Calendar") I started to edit.
    (I took out the addresses from Metal Production for sake of privacy.)

    My goals are to get the function of auto population in Demo Calendar ported to Metal Production, OR the format of the Metal Production ported to Demo Calendar (whichever is easier).

    I only need the "PO: Name" to populate into the calendar, and of course I would need the date to extend over the range ("Start Date" to "End Date").
    I would also like to change to calendar to grey out the days of other months (instead of black out), and maybe color code the jobs based on crew. However, the color code may not be necessary if it could populate the calendar in order based on the crew (i.e. the same crews are 1st, 2nd, etc. on the calendar day).
    Attached Files Attached Files
    Last edited by mkopenhagen; 11-16-2021 at 12:56 PM. Reason: forgot to upload workbooks

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I note there are frequent occurrences of a crew having more than one "PO:Name" for a given date range so what is the expected output?
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Thank you. That is another kink I have to work out. I'm running into more and more kinks. I'm attaching a new file that I have mostly in the format I would like, and mostly working.

    With regards to that issue, I think the best solution would be to have each "PO: Name" have it's own line, and then have conditional formatting to color code the crew it is assigned.
    I would also like this conditional formatting to extend to the Data Table.

    On another note, I was trying to add another line onto the calendar (as you will see), and I am running issues. It is returning the value based on a cell instead of the date.
    We're also running into the issue of sharing the file. Half the point is to share (without editing privileges) the data table (not the calendar, because we don't want them to see our holes) to the whole company.
    Attached Files Attached Files
    Last edited by mkopenhagen; 11-02-2021 at 12:14 PM. Reason: attaching file

  4. #4
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I figured out how to add the extra line.

    However, I'm still stuck on how to share this.. This will be completely in vain if we can't share it.

  5. #5
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Somehow the share function magically started working.. so nvm on that.. Working on the conditional formatting and having the job span the calendar to the End Date.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Having spent some time on this I have failed to find a formulaic solution to inserting multiple entries for a date (or date range).

    The answer maybe to use the technique in the attached (courtesy of Pete_UK) which means generating the equivalent of the "Activities" sheet.

    The Conditional formatting too could be "tricky" as it requires matching the "PO:Name" to the crew (but I haven't given it much thought).

    As you have OS365 there may functionality which will enable a solution with your current data.
    Attached Files Attached Files

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    What I have done in the past for posters who want to use a date range (i.e. From and To dates) instead of a single date is to generate another table made up of single dates, and then use this as the source for the calendar. This is done automatically.

    I'm just about to log off for a few hours, but here is one that illustrates what I mean. I'll check back later to see if there are any further comments.

    Hope this helps.

    Pete
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Formatting Data of an Auto Populating Calendar Workbook

    See attached using Pete's calendar: VBA routine to create "Events_Data"
    Please Login or Register  to view this content.
    There are numerous duplicate entries in your data.
    Attached Files Attached Files
    Last edited by JohnTopley; 11-03-2021 at 01:56 AM.

  9. #9
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    My goodness.. y'all are excel gods.. All this is a little over my head.

    I'm going to upload the last version I got to before you uploaded my version with the Events_Data sheet.

    So really the only thing missing now is the formatting of the calendar events based on which crew it is assigned to.. I don't know if this is possible, but it seems like excel has nearly no limits, lol.

    Well, setting permissions is the last thing, but that'll be just before it goes live.
    Attached Files Attached Files
    Last edited by mkopenhagen; 11-03-2021 at 08:04 AM. Reason: attaching

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I've copied your data across to my file from Post #7, so you can see it in operation. Up to 10 "events" can be displayed for each day, and you can choose the month and year in the yellow cells in column K.

    Conditional formatting is applied to give alternating blue shades (for clarity), but these are not linked to the names (you would need a lot of colours, and therefore CF rules, for that).

    Note that "orphan" days at the end of the month are shown on the top weekly block, so you only need up to 5 weeks to display each month. This happens about 2 or 3 times a year.

    This is all done by formulae, and you just keep adding data to your table as required. Consequently, I have saved the file as .xlsx - I noticed that your file was .xlsm, but I didn't bother looking at the macros in it.

    Hope this helps.

    Pete
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Formatting Data of an Auto Populating Calendar Workbook

    In your file I added a "helper" column in "Data Table" column N [ to match calendar entries of PO & Name]

    =A2 & " - " &B2

    For CF

    =AND(A6<>"",INDEX('Data Table'!$I$2:$I$100,MATCH(A6,'Data Table'!$N$2:$N$100,0))="Walter")

    Set FILL colour as required

    Change rule for each crew i.e. amend "Walter"

    Assumes the "PO:Name" is allocated to a single Crew

    Question: why use $D$2 for date rather than $B$2 ??

    And are you planning to use Pete's workbook to give the daily record ?
    Attached Files Attached Files
    Last edited by JohnTopley; 11-03-2021 at 09:00 AM.

  12. #12
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Okay this is awesome! I just uploaded the most recent file with all the conditional formatting!!
    The only thing I'm unsure of with the CF is if it IS NOT any of the crews with particular formats (our in-house crews), I would want it formatted as a separate color. So I need to change that CF formula you gave me to an IF NOT TRUE of "Alonzo", "Chepe", etc.. RETURN whatever other color I choose to set it up as.

    To answer your questions, I just started filling in the template I dl'd from this forum. I couldn't figure out how the Calendar was referencing the columns because it didn't matter where I put the column. So I just kept moving it around until we got it to where we wanted it. We decided we only want the PO & Name on the calendar, so we really don't even need it to reference 2 columns, but I don't know how to change it. Maybe if I could, that would erase the need for column N.

    And as for Pete's workbook, I'd like to port the Event_Data sheet into my current version of the Metal Production to accomplish the auto-fill of the multi-day jobs into the calendar. I prefer the 6 weeks, so we can see the overlap of the schedule from previous and next months. I also like the drop down for choosing the months.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Formatting Data of an Auto Populating Calendar Workbook

    For other than "in-house" crews use a generic name if you are using a single colour. the "NOT" option is "no-go"!

    Re column N, why change (you have 16K columns to play with): you need this data for the "lookup" against the calendar so the other option is to manually enter the "PO" & "Name" combination.
    Last edited by JohnTopley; 11-03-2021 at 12:58 PM.

  14. #14
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Okay, I copied the array into my Metal Production Sheet. Now I just have to get the calendar I have set up to reference that instead.. Then get that last CF if it isn't one of the in-house crews.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Quote Originally Posted by JohnTopley View Post
    For other than "in-house" crews use a generic name if you are using a ingle colour. the "NOT" option is "no-go"!

    Re column N, why change (you have 16K columns to play with): you need this data for the "lookup" against the calendar so the other option is to manually enter the "PO" & "Name" combination.
    Ah! Brilliant. That definitely would just make it easy.

    Re column N, I just meant I could combine A & B to make the same thing, then reference that instead of making the new column for reference. Only problem is how the calendar is currently set up to populate; it needs to be two columns.
    However, we need to change it anyway to populate from the array that breaks down the jobs into multiple single entries for the multi-day jobs.

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Column N IS a combination of A & B so you can copy/paste values from N but for new values of "PO" & "NAME" this will be manual entry.

    Using Pete's calendar, it is a single field and yours could be by using column N instead of A & B combination.

  17. #17
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I think you're missing my point, but it doesn't really matter.

    I'm trying to break down this formula:

    =IFERROR(INDEX(Events_Data!$G:$G,MATCH(C4&"_"&$A6,Events_Data!$F:$F,0)),"")

    (This is from Paul's version.)

    So I can populate with the entries in the array that breaks down the multiple day jobs.
    Last edited by mkopenhagen; 11-03-2021 at 01:49 PM.

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Formatting Data of an Auto Populating Calendar Workbook

    No.. I don't understand: Pete's w/book already creates the daily entries ("Events_Data" columns A:G) from the data in columns I:U of "Events_Data": the latter is copy of your "Data Table".

    Simply modify the title and date range i.e add your drop-down (But change formulae to reference your date field )in his book and you will have a working version, minus the CF!
    Last edited by JohnTopley; 11-03-2021 at 02:43 PM.

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    That's why I've not really chipped into this thread today - you already have most of what you want in my workbook, which can then be more easily modified to give you the CF colours for the crew. You could also have the crew names listed for each line within each weekly block, and then the colours are even more-easily applied. It is also quite easy to have a static 6-week block, with non-month dates greyed out.

    I don't understand why you seem to be wedded to the earlier file that you downloaded.

    Pete

  20. #20
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Okay, no problem.. I will start editing the other workbook you uploaded. How do I add the 6th week?

    And I am definitely a novice, I don't even know how to add the drop down >,< ..

    Learning a lot from you both, though. Even if most of it is above my head.
    Last edited by mkopenhagen; 11-03-2021 at 03:14 PM.

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Is it a rolling 6 week period as this will affect the date calculations in any calendar [i.e. 1st of a month mat no longer be the start date on your calendar] ?

  22. #22
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I am also running into some issues when I try to delete a line from the data (I:U or even the entire line). Specifically when I delete U (the Start Date) it returns the whole Event_Data array as #REF!.

    What am I missing?

  23. #23
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Quote Originally Posted by JohnTopley View Post
    Is it a rolling 6 week period as this will affect the date calculations in any calendar [i.e. 1st of a month mat no longer be the start date on your calendar] ?
    Um. I haven't thought about a rolling 6 weeks. I was assuming that it would be the typical calendar format since that's how it is currently selected. If it were rolling how would I choose which 6 weeks I'm looking at? I guess just input a particular date to have it start from.. idk that might be more than we need. I think a regular calendar month would be easiest to deal with. Just having the days from previous and following month (if there are any) show up slightly greyed out.

    Although the more I think about it as I write this, maybe a rolling 6 weeks would be good... I'll have to consult with my team and get back to you about this.

    Edit: They want a traditional calendar format, with the days of the previous and following months a little greyed out, but info still on them.
    Last edited by mkopenhagen; 11-04-2021 at 08:54 AM.

  24. #24
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Quote Originally Posted by Pete_UK View Post
    That's why I've not really chipped into this thread today - you already have most of what you want in my workbook, which can then be more easily modified to give you the CF colours for the crew. You could also have the crew names listed for each line within each weekly block, and then the colours are even more-easily applied. It is also quite easy to have a static 6-week block, with non-month dates greyed out.

    I don't understand why you seem to be wedded to the earlier file that you downloaded.

    Pete
    Honestly I just don't know how to edit the format all that well and I put so much time into the other one, I just figured it would've been easier to edit that one.

    But in reference to the CF having the crew names listed for each line, I thought about that. However, we run into the issue when they have more than one job in a day.
    You say all this is easy, but easy for you is not easy for me. O.O lol

    I very much appreciate both of your help thus far.

  25. #25
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I'm working on editing the other schedule today. I think I can break down the CF stuff, but how do I add a sixth row for the Calendar, and have it populate that way?

    Also, I still haven't figured out what the issue is when I try to delete a line and having it through the whole Data array into chaos.
    Last edited by mkopenhagen; 11-04-2021 at 09:05 AM.

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I have been making some changes to my file from Post #10 in line with what you are asking for. It is not finished yet, but I have to go out soon so I thought you might like to see the current status.

    I've set the calendar up so that it displays 6 consecutive weeks, with dates before and after the month selected via the yellow cells in column K. The 1st of the month will always appear in the top week, so there may be a few days before that and at the end of the 6 weeks which relate to different months, and these are greyed-out, as in your other calendar. Up to 10 different "events" will be displayed for each day of the calendar.

    I've inserted a new column (H) in the Events sheet to pick up the Crew member in the expanded dates - this will be used for the conditional formatting. At the moment, the CF on the calendar still shows alternating shades of blue, but you will see a Key that I've set up in column K to indicate a colour for each member of the Crew. I shall set this up later, though it will be a bit tedious to set up 10 different rules.

    Hope this helps.

    Pete
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Oh shoot! That's amazing! Now I don't know what to work on, lol.

    Thank you so much!!! I'll be patiently waiting.

    If you do just one of the CF, I can probably figure out the rest to save you some time.
    Besides, the person inputting the data didn't really need to put "Chepe C." Because even though we have two Chepe's, we only schedule one for the Metal Production side.
    Last edited by mkopenhagen; 11-04-2021 at 09:38 AM.

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I think this is more-or-less finished now - time for you to give it a thorough testing out.

    I've left Chepe C in, but you can always re-use that name for someone else if you like.

    There's not much more to say about it. The colour coding is as per the Key in column K. I've set up two named ranges Ref and Crew on the Events sheet, which the CF rules refer to.

    Note that the formulae in the left hand side of the Events sheet have been copied down to row 1000, but you may copy them further (and adjust those named ranges) if you need to. Bear in mind that a single record in your data set gets expanded for as many days that there are in the date range, so if you have a date range which covers 30 days, then that will need 30 records in the expanded set. Thus, you can very easily use up the 1000 rows that the coloured formulae have been copied down to.

    In operation, you would add data to your data table (columns J to V in the Events sheet) in any order, and then it will appear on the calendar on the correct date(s). You can omit the End date if the event is just for a single day (i.e. it will assume 1 day duration if the end date is blank). You don't need to do anything with the formulae in columns A to H (other than ensuring that they have been copied down far enough to cover your data), and you can hide these columns if you like to avoid them being changed accidentally.

    So, over to you now - let me know how you get on.

    Hope this helps.

    Pete
    Attached Files Attached Files

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I hope you haven't made very many changes to the earlier workbook, as I spotted a few things that needed to be changed, so you should use this version instead.

    I've corrected the first date calculation, as well as tidied up a few old named ranges. I've also amended the CF rules - earlier, I had hard-coded the names within the rules, but now I have linked them to the names in column K on the calendar. There is no visible change to what it looks like, but now if you had a new Crew member (e.g. "Dave"), you could just put that name within the Key (e.g. to replace Chepe C), and the CF rule would respond to it.

    Hope this helps.

    Pete
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    You're the man!

    I will check it out today and let you know if I have any questions or run into any issues.

    You're really a life saver. I appreciate you more than you know.

  31. #31
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    So off the bat, I'm still having the #REF! issue with B:H when I try to delete a line in J:V. Specifically, the problem happens when I delete the "Start Date" (column Q).

    I was looking at the formulae, and I couldn't figure out why it was having the issue.

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Are you deleting the cell contents, or the complete row?

    I designed the formulae assuming that the raw data was contiguous, so I didn't expect any data to be deleted.

    Pete

  33. #33
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I've tried both ways. It was just a duplicate line, so I was trying to clean it up.

    Otherwise the data being contiguous shouldn't be an issue as we would just add it at the bottom and use the filters to arrange as needed.

    However, I do see mistakes happening and potentially needing to delete data in the future for various reasons.

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    The system for issuing email notification of replies is not working at the moment, so I have only just seen your reply. One way to avoid having to delete a record is to copy everything below the offending row in your data table (plus one blank row), and then paste the copy over the offending row.

    Another thing I thought of to enhance the calendar sheet was to produce a summary table next to where the key is, so that you would have weeks 1 to 6 in consecutive columns, and then for each name you could sum the days within each week using a SUMIFS formula, and then have totals across and down so that you could compare the workload for each member of the Crew.

    Hope this helps.

    Pete

  35. #35
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Okay, that should be a good enough work around for getting rid of rows. Thanks.

    I'm having trouble visualizing what you mean. I think you mean a table with the number of jobs each crew would have in a given week?
    If that is what you mean, I think it's a cool idea, but not really necessary for our needs.
    Each crew is only ever working one job at a time. The number of jobs they have in a week (or even day) just depends on the scope of the job.

  36. #36
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Could you tell me if it's possible, and if so how, to set the permissions so that we have 2 types of permissions.
    1) To have most people able to see the first worksheet, but unable to edit, and unable to see the 2nd worksheet.
    2) To have a few people able to edit and see the entire workbook.

    edit:
    I'm still trying to figure this out. I couldn't find anything on this forum. (I might not be utilizing it properly; I just did a simple search.)
    I did, however, find a VBA on another site that might work, but if you turn off macros, then it would be null and void.
    Any ideas or recommendations are welcome.

    (The post following this one I figured out.)
    Last edited by mkopenhagen; 11-10-2021 at 08:49 AM.

  37. #37
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    __________
    Last edited by mkopenhagen; 11-09-2021 at 04:28 PM. Reason: Deleted question: figured it out

  38. #38
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    So I can't get the VBA code that I found to do what it is intended. (Really I can't get it to do anything..) I will upload the file to see if I'm missing something.

    However, I do have a couple concerns, even if I can get it working:
    1) When you open the file, it prompts you to enable (or not) Macros. If the user chooses not to enable, will that make this whole thing void?
    2) Will this code work once it becomes a sharable file and it's auto-saving? I know we can set it to where the users only have access as read-only.
    Attached Files Attached Files

  39. #39
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Remove password protection!

  40. #40
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Whoops. The password is just "test"

  41. #41
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    I had an idea that if we can't get the permissions how we want them, maybe we could just separate them into two separate workbooks.
    Even though that would be a pain.
    However, idk how/what to change in the code on the calendar sheet so it would still correctly reference our data spreadsheet.

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    The easiest way is to arrange the Excel window so it does not cover the whole screen, and then you can just drag the tab for the Events sheet to an area outside the Excel window. This effectively moves the tab into a new file window, so you can use File | Save As to save that new file with a new name. You could also use File | Save As to save what is now just the Calendar sheet in the original file with a new name, so you still have the original file should you need it.

    I'm not sure how this will affect the Conditional Formatting, so give it a try and report back.

    Hope this helps.

    Pete

  43. #43
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    So that did work. However, like you suspected, the CF is a bit messed up.
    Somehow it changed some of the colors, but when I try to change them back it says, "You may not use references to other workbooks for Conditional Formatting criteria."
    So it kind of works.. I'll upload them.

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    In that case, you might have to arrange it so that you have three sheets in your original file - one for (just) your own data entry table, another for the expanded dates section (i.e. the coloured columns that I put in), and the calendar itself. Then you would be able to separate out the data entry sheet into a new file, as the rules governing the CF relate to areas of the expanded dates.

    I'm going out soon, but I'll check back later to see how you get on.

    Pete

  45. #45
    Registered User
    Join Date
    11-02-2021
    Location
    Louisiana, USA
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit
    Posts
    26

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Ah! That worked!!

    Thank you so much.

    I think this workbook is complete (for now, lol).

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

    Re: Formatting Data of an Auto Populating Calendar Workbook

    Well, that's good to hear - thanks for feeding back.

    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] help auto populating a calendar
    By atrout in forum Excel General
    Replies: 18
    Last Post: 03-13-2019, 04:26 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. Auto-Populating Calendar
    By emk123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-13-2017, 07:29 AM
  4. Auto Populating Calendar
    By NEWBie32 in forum Excel General
    Replies: 2
    Last Post: 01-19-2016, 06:56 PM
  5. 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
  6. Replies: 2
    Last Post: 04-28-2014, 05:17 AM
  7. Replies: 0
    Last Post: 02-21-2013, 09:04 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