+ Reply to Thread
Results 1 to 67 of 67

Year Long Schedule

  1. #1
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Year Long Schedule

    Hello All,

    I come with my hat in hand, I promise I have searched and searched for the answers to how to accomplish just what it is I am trying to do, all to no avail. I am in the US Navy and of course you are not allowed to install any program that would make life easier so you make due with what is already installed, luckily Excel is one of these programs and normally I can eek out everything that I need from it, this time however I am at a roadblock I, by my self cannot get past. I know it is probably possible just need some guidance, so to the meat of the problem...

    I am trying to create a year long calendar to track my Sailors, when they have school, leave, underway time, things like this. My chain of command wants a visual representation of this. The first tab in the included file is what they were working from when I got to the command. The next 5 tabs are the conversion I have made so far, much to my disappointment. The last three tabs are from a scheduling template I downloaded from https://trumpexcel.com/excel-leave-tracker/. On my 5 tabs I am attempting to divide my command into departments, however I also do need a tab that recombines the entire command so people can see at a glance during briefings what the status of the whole command is. The major roadblock that I am running into is that for any of the absences we have my Commander wants to have a merged color coded block on the days they will not be at the command (as you can see from the other tabs). I can merge and center them then use some conditional formatting to achieve this however I cannot find any way to get this to replicate on the command wide tab without entering all the data and remerging the cells on the other tabs. I would like to have the ability to have the department head from each department only worry about the Sailors on their departments tab and then get that automatically update the Command tab. If we really get into what I want, I would love the ability to have a sheet that has start / stop dates and reason for absence and get that to self populate the department tabs, kinda like if I had a form and say Sailor X will be in school starting on 1/17/2017 and ending on 1/30/17 then it would go to the corresponding table and merge the cells, and highlight them. Anyway, my current work around is to use the camera tool but it does some weird things with formatting and if your area is too big it loses focus (image is blurry) so I really dont want to go this route. I love the layout and the abilities from the template I downloaded just still running into the same roadblock with that sheet as well.

    Any help would be greatly appreciated, I have been beating my head against this particular wall for about three weeks now, and still have not made any progress. I am sure that there is something that can be done with vlookups, matches, and and / not statements, but I just cant get them right.

    Thank You in advance,
    Ken
    Attached Files Attached Files

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Year Long Schedule

    can you give specific reference with cell location. Instead of "first tab" / "next 5 tab", / "last three tab" mentioned specific sheet name with cell location.
    Also i have notice that, in some cell is "merged cell" my suggestion is remove those merged cell, because it is difficulat to find which cell merged or single cell. Every time go to location & find.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Year Long Schedule

    It is not good practice to use merged cells BUT if your Commander insists then you will need VBA to do this: it cannot be done with formulae.

    Is this (VBA) a potential "show stopper"?

    The attached has "Leave Tracker" and "Leave Tracker 2" working (uses VBA already).

    Do you want to use these sheets for your Departments?

    And use the same format for consolidation into a "Full command Sheet" which would certainly require VBA to accomplish?
    Attached Files Attached Files
    Last edited by JohnTopley; 01-29-2017 at 10:10 AM.

  4. #4
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    @Avk and @JohnTopley,
    Thanks for offering to help, I am gonna need it. I will try and answer both sets of questions. Merged cells are not an absolute requirement but he likes the visual that you get from having them. I know that on a single sheet you can highlight all cells for the range and you can center text across and fill all cells the same color with a border around them with no inside borders so that it looks like a merged cell even though really it is not. Visually this would look the same and I have no problem with it, however my problem remains as I have no idea how to only have to do this process once and get it to automatically replicate on onto the consolodated tab. VBA is also not a deal breaker, I should be able to get my IT guys to allow it, my coding skills however are lacking.

    So @JohnTopley the heart of the problem is this... I would like to have the department heads work on their departmental sheets, ie the Supply Officer would only need to work on the Supply worksheet, then I would like to have all the data and formatting replicated into the Full Command worksheet so that if we do a briefing or command level scheduling we can see all departments at one time.

    @AVK continuing with the explanation, as a specific example, on the Supply worksheet, employee 12 has a school starting 16 January 17, for a visual representation of this the date range of the school has been merged, centered, and filled blue. What I am trying to accomplish is after the data such as the school date range is entered once, it will also replicate and you will "see" the same representation on the Full Command worksheet.

    Ideally, I agree we would lose all merged cells and just have a different solution for the visual effect. Also Ideally I would be changing the whole format to look and operate like the template I downloaded which is the Leave Tracker worksheet. I like that it uses a slider to show only one month at a time, that it auto shades non workdays and holidays. @JohnTopley I am unsure what it is you did to this worksheet, I looked at the VBA and see the changes you made but unsure of what they are actually doing. The template that I downloaded is working fine by itself but it does some minor weird things when I copied the sheet into my workbook, this is not an issue as I dont intend on copying like this, if I figure out the highlighting I will use the template as a base.

    This is getting really long and I am sorry, I just want to accurately convey what it is I am trying to do so people that are offering to help are not working hard on something that is not actually what I am trying to accomplish. On another note, I think I am getting close, here is what I have going so far...

    I started a new worksheet that will have every employee name, the reason for absence, as well as start and stop dates of absence. Using a formula in conditional formatting it will look at the date of the column, then vlookup employee name against this new worksheet if the date is greater than or equal to the start date and less than or equal to the stop date then it will format the cell. I get this to work but only in the first cell, I am having trouble getting it to copy down when the range is changed to the whole target area. I tried to ensure that I used the relative and absolute values as required but I am missing something, here is the code I have in the conditional formatting as well as a stand alone of the new sheet I created.

    Thank You again for all your help,
    Ken

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Year Long Schedule

    Re "Leave Tracker":

    The amendments I made were simply to allow "Leave Tracker" and "Leave tracker 2" to work independently. If you look in Sheet3 Columns G and H you will see parameters which relate to each tab. These will need to be extended for every "tracker" sheet.

    I also modified the VBA code to reference the active sheet so it knows which "tracker" tab is being changed:

    Please Login or Register  to view this content.
    The highlighted code is the original as it was designed for a single "Tracker" sheet.

    Tracker works simply by entering in the Month tab which is stored in columns which are "hidden". If you want to use the lists as per your last example then you have 2 choices: add formulae to all the hidden cells to "lookup" your data lists OR use VBA to do the same.

    Re your latest file: it better to have the organised in a single list as the attached.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Year Long Schedule

    Another option ...
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    @JohnTopley

    Thank You so much John, I think we are well on the way to getting this working. I was up to 3am this morning working on this and ended up falling asleep at the keyboard researching some different way to accomplish my goal using indirect formulas. I really like what you have going here, my reason for using multiple columns instead of multiple rows was two fold, 1 I was trying to use vlookup so needed unique identifiers in each row, and 2 the one that really gets me is that once I get this up and running I have 100+ Sailors to input, if we believe that each one will have between 20 and 30 absences over the course of a year that makes for upwards of 3000 rows vs 100 rows with up to 60 columns. If you can come up with a way to do it using columns vs rows that would be awesome, if not I will continue to work the original issue using rows. Again thank you so much for all that you have done so far, you have taken me much farther along than I was.

    Ken

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Year Long Schedule

    In my view you will have to use VBA if you really want to have 60 columns of data.

    You could have a VBA macro to transform the column format into the row format which is really the only way you can use the formula I provided to fill the calendar.

  9. #9
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi there,

    This seems like an interesting project - not necessarily straightforward, but interesting!

    I imagine we have to assume that there can be a Sailor Elvis Presley in Air Ops, and another Sailor Elvis Presley in Combat?

    With luck I may be able to post something here tomorrow/Tuesday.

    Regards,

    Greg M

  10. #10
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    @Greg M,

    Surprisingly no we wont run into that issue as the Rate / Rank column will divide them out. Generally speaking your rate (designation for the job you perform in the Navy) will determine which department you work in. Beyond that if we do happen to have say two GSMC Parkins in Exec department then we will separate them using last name comma first initial, so it would be GSMC Parkins, K and GSMC Parkins, R. Thanks for your interest and your willingness to help.

    Ken

  11. #11
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Many thanks for your very prompt feedback.

    Ok on that - so far this project just gets interesting-er and interesting-er

    Will post my pennyworth here asap.

    Regards,

    Greg M

  12. #12
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Getting Really Close, Just need one last thing

    Quote Originally Posted by JohnTopley View Post
    Re your latest file: it better to have the organised in a single list as the attached.
    @JohnTopley

    Hopefully you are still around and willing to help just a little bit more. I can work with the multiple rows, not as efficient as I would like but probably the easiest solution, also it will be easiest to explain to a 25 year old with no computer experience how to enter the data. For now I have only been working on the Full Command sheet, once I get it up and running Ill duplicate everything over to the other sheets.

    I think I have just about gotten it all told, I still have at least one small error as the codes are not propagating from the absence input worksheet (new worksheet added in the attached version). I attempted to modify your code from your Tracker.xlsx but for some reason it is returning an error. At first I thought it was simply because where you have a full date in your Jan 17 sheet B2 cell, my cell is just a day date, no month or year. So I changed the formula to concatenate a full date to no avail, then created a new row with the date concatenated also to no avail, so just to check i manually entered the date like yours is but it still returns an error. Here is your code compared to mine, hopefully you can help me fix this last thing and then I think I can get it into all the other sheets without problem. I have attached my new workbook with everything put together so you can see it in action. Once again thank you and everyone else that has helped me with this workbook, I appreciate it so much.

    Ken

    Your Code
    Please Login or Register  to view this content.
    Versus Mine
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by parkins5322; 01-29-2017 at 08:14 PM.

  13. #13
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi again,

    Take a look at the attached workbook and see if I'm going in the right direction.

    I haven't bothered with the Leave Tracker at this stage, instead I've concentrated on updating the Full Command worksheet with the data that are inserted on the individual Department worksheets.

    I've been down the "Calendar Road" many times, and one of the most frustrating things is that after you've put so much effort into adding day names, shading Saturday/Sunday cells etc., you've got to do it all again once a new year comes along. Also, sometimes you need a calendar which starts with January and other times you need one which starts in (e.g.) October, which means another lot of formatting! To avoid this, I've also provided automatic formatting for the various calendar headers - i.e. when the date in Cell A1 of the Full Command worksheet (i.e. the day on which the twelve-month period begins) is changed, all calendars in the workbook will update automatically to reflect the new starting date.

    Anyway, take a look at what I've attached and see what you think.

    Regards,

    Greg M
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    @Greg M

    Wow, thats awesome man. I will have to make sure that my IT people will let me use macros / active x controls, they are a picky lot. But from what I see thats amazing so far, with the functionality you have there and some of the formatting and other stuff I have worked on in other workbooks I didnt post, I think this can accomplish my goals.

    I am not ruling out all the work that JohnTopley has done either, they are both fantastic directions to go in but both are drastically different. I will work a little more with both of them tonight and then tomorrow afternoon. I have school for most of the day tomorrow so wont get a chance to work on either till mid - late afternoon. Once I get a little more done I will let you guys look at them and see what you guys think.

    Thanks again for your work on my project.
    Ken


    Edit: @Greg M
    A couple of thing I did notice already though is if I change the start date in cell A1 of the full command sheet it changes all other month headers to the same month. So when I change it to 1/1/2017 all of the month blocks have a header of January 2017. Additionally I will have to figure out a way to get your macro button to adjust when new people are added to the department sheets. Test run shows that if I add names and then hit the update macro button it does not show the names I added. I know there is a way in vba / macro you can select all the way to the first empty cell, I am unsure if that is the way we should head or if there is something better.
    Last edited by parkins5322; 01-29-2017 at 08:39 PM. Reason: Comments for Greg M

  15. #15
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    @Greg M

    Ok I may have jumped the gun on that last comment, I see now that you have to copy the name and rank from the department page to generate the name and rank list on the full command sheet, once that is done then the update works flawlessly. Ill keep playing with it.

    Ken

  16. #16
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi again Ken,

    Thanks for that feedback - you must have been burning the midnight oil again last night!

    Regarding the date entered in Cell A1, I'm assuming that all Department worksheets should correspond to the same 12-month period, Cell A1 contains the starting date of that period. I can certainly move that date to a "Jotter" worksheet.

    One of the next items on my agenda is to provide a facility for specifying the period which is displayed on the Full Command worksheet - this will basically hide/unhide the appropriate columns on that worksheet. The worksheet will always contain data for the full 12-month period, but only the requested columns will be displayed.

    Ok on your comment about the need to enter names on the Full Command worksheet. I'd assumed that the sequence of the names on that worksheet might not necessarily be the same as the name sequences on the various Department worksheets - e.g. the Full Command worksheet might need to display all names in Rank and/or alphabetical order rather than by Department. If this is not the case it's not to hard for me to just "grab" everything from each of the Department worksheets and "dump" the data in that sequence on the Full Command worksheet.

    I'd be glad to have your comments on the above when it's convenient for you.

    Regards,

    Greg M

  17. #17
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Greg M,

    I like the way you have cell A1 set up, it just does not seem to function correctly on the tracker I downloaded from you. When I change the date so that it becomes the new start date, ALL the monthly headers change to January 2016. I was looking for how you coded those blocks or the code for the A1 cell but cant see where you have that hidden so I cannot see what is going wrong. The "next item on your agenda" is pretty much how the template that I downloaded works, it uses a scroll bar to advance the months then only displays the single month. On the most recent workbook I have posted this is how I have all the Department and the Full Command sheets set up. This is set up with a very small piece of VBA code and a couple of objects, the code is posted below. As for the names, my intention is to always have them sorted by last name or possibly by rank, I like to use name since those are pretty close to being individualized, and makes it easier if I am looking up Sailor Elvis Presley instead of having to know what rank they are.

    Again thank you for your help on this, like I said so far it is looking amazing and if we can combine the work you have done with the template and / or with the work JohnTopley has done, I will be all set.

    Please Login or Register  to view this content.
    Last edited by parkins5322; 01-30-2017 at 01:34 PM. Reason: forgot to add the code

  18. #18
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Quote Originally Posted by Greg M View Post
    Regarding the date entered in Cell A1, I'm assuming that all Department worksheets should correspond to the same 12-month period, Cell A1 contains the starting date of that period.

    Ok, I see where you have all this formatting done in VBA, I even understand the majority of it, but I dont see anything wrong with it that would lead to the discrepancy that I am seeing. I have tried several times now and always the same result, if I change the date in cell A1 all the month headers turn to January 2016. I am sure it is something small and easy but I dont see it. Of other note I have to admit I dont understand the use of the asterisk in VBA. It is both in the code I listed above and I dont understand how / what it is doing and in your Month Header VBA you have it used as...

    Please Login or Register  to view this content.
    Again I dont understand how / what it is doing, I think if I did I would understand all but just a tiny bit of how your headers are generated.

    Ken
    Last edited by parkins5322; 01-30-2017 at 01:57 PM. Reason: again forgot to add code /sigh

  19. #19
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Just a quick update.

    I don't have the latest version available to send you just yet, but it should be ready fairly soon.

    Just a clarification: the value in Cell A1 (which has now been moved to a separate worksheet) is intended to be changed only once per year - i.e. when you're creating a new calendar for a new twelve-month period, otherwise it's never altered.

    The latest version has a "Display Period" button on the Full Command worksheet - clicking on this displays a UserForm which allows you to select whatever month(s) you're interested in and only the appropriate columns will be displayed - i.e. you can select e.g. November 2016 or e.g. May 2017 - August 2017.

    I'm working on a UserForm which allows you to select any Employee Name on a Department worksheet and enter an activity, a start date and an end date, and the appropriate cells on the worksheet will be marked accordingly.

    It's still interesting!

    Regards,

    Greg M




    P. S. Further to your last post, in the latest version, all calendars pick up the correct monthly headings when the value in "Cell A1" is changed. I thought that this was the case with the previous version I posted, but perhaps not.

    I'll post the latest version as soon as I can, but it might be tomorrow morning before I can do it.
    Last edited by Greg M; 01-30-2017 at 01:51 PM. Reason: P. S. added

  20. #20
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Greg M,

    Ok, so I figured out all the VBA and I figured out what the problem was, all the month headers were changing to January 2016 because of the format. Excel natively uses mm/dd/yy so even if you change a cells formatting to show something different excel still understand only mm/dd/yy then will show you whatever format you want. Cell A1 was formatted to show your custom date format which was dd MMM YYYY but the value in the cell was 1/1/2016. When this is changed to 2/1/2016 cell A1 appeared as 1 FEB 2016 but the first cell in the header of the first month shown actually changed to 1/2/2016, and the next was 1/3/2016 and on and on. Simple fix, I changed the order of the code in VBA and walla it works as intended now. Below is your original code followed by my modification.

    Your Original Code
    Please Login or Register  to view this content.

    My Modified Version
    Please Login or Register  to view this content.
    Ken

  21. #21
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Quote Originally Posted by Greg M View Post
    Just a clarification: the value in Cell A1 (which has now been moved to a separate worksheet) is intended to be changed only once per year - i.e. when you're creating a new calendar for a new twelve-month period, otherwise it's never altered.

    The latest version has a "Display Period" button on the Full Command worksheet - clicking on this displays a UserForm which allows you to select whatever month(s) you're interested in and only the appropriate columns will be displayed - i.e. you can select e.g. November 2016 or e.g. May 2017 - August 2017.

    I'm working on a UserForm which allows you to select any Employee Name on a Department worksheet and enter an activity, a start date and an end date, and the appropriate cells on the worksheet will be marked accordingly.

    P. S. Further to your last post, in the latest version, all calendars pick up the correct monthly headings when the value in "Cell A1" is changed.
    Hi Greg M,

    I understand cell A1 and got it working even though now it may have been a moot point as you have moved it lol, however it was good for me to figure it out and remember / learn a little more about excel / vba. Your latest version with forms sounds fantastic and very user friendly (... so easy even a Junior Officer could do it). Below I have uploaded my latest version using the downloaded template and modifications by @JohnTopley on this version you can see how I was setting up a sheet for absences (I believe these are all the ones I will be using) to work similar to the UserForm you are creating, not sure if there is anything there that can help you accomplish your goals but if there is any way that I can help you to help me just let me know, I will do whatever I can. I really like the idea of a UserForm and that was my original plan but have no idea how to code one of those. In post 12 of this thread I directed a question of code to @JohnTopley and compared his original code to my modified version, again not sure if it will help in your work at all but it was designed to take the absence code from the absence input sheet and replicate it on the FullCommand sheet. As for your PS, no worries it was good for me and eventually I did figure it out.

    Again I cannot say how much I appreciate the work you and others are doing on this, I am glad it interests you and drives you to see if you can accomplish it. It will be a huge boon for my command and I and will probably be used for quite some time.

    Thank You
    Ken

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Year Long Schedule

    Re query in post #12: formula must be entered with Ctrl+Shift+Enter.

  23. #23
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi again Ken,

    Latest version attached - still some tweaking/bulletproofing to add, but see what you think of things so far.

    The Cell A1 issue - thanks for all of your comments, and well done on overcoming the date format inconsistency. In the latest version the first day of the 12-month period is specified (on the Jotter worksheet) in three separate Day/Month/Year cells, so this should make things independent of local date formats.

    I've added a "Select Period to Display" button on the Full Command worksheet - this will shrink/expand that worksheet to show only columns for the months you choose to display.

    There's a new "Specify Absence" UserForm which allows the details of individual absences to be entered. To use this, right-click on any non-blank Name Cell on a Department worksheet, and the UserForm will be displayed. At present the information entered on the UserForm will be displayed in a MessageBox - I'm working on having this information transferred automatically to the Department worksheet, but at least you'll be able to see what I propose as a User interface. Note that this facility is available only on the Department worksheets, not on the Full Command worksheet, but I imagine that all of the data entry will be done on the Department worksheets anyway.

    The UserForm contains a dropdown list of "standard absences" - will you need an equivalent list of standard duties/activities?

    The text (if any) in the Description TextBox on the UserForm is appended to the abbreviated form of the "standard absence" and will (eventually! ) be entered in the absence "bar" on the Department worksheet.

    The "Update" and "Clear" buttons remain on the Full Command worksheet, but their locations have been changed slightly.

    Have a think about whether or not you want to retain the facility of being able to arrange names on the Full Command worksheet in a different order from that on the Department worksheets. Obviously this means that the names must be entered on the Full Command worksheet in the required order rather than just being "grabbed in bulk" from the department worksheets.

    Hope this helps - please continue to keep me informed.

    Regards,

    Greg M
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Quote Originally Posted by Greg M View Post
    (Note 1) The Cell A1 issue - thanks for all of your comments, and well done on overcoming the date format inconsistency. In the latest version the first day of the 12-month period is specified (on the Jotter worksheet) in three separate Day/Month/Year cells, so this should make things independent of local date formats.

    (Note 2) I've added a "Select Period to Display" button on the Full Command worksheet...
    (Note 3) There's a new "Specify Absence" ...
    (Note 4) ...will you need an equivalent list of standard duties/activities?
    (Note 5) The text (if any) in the Description TextBox...
    (Note 6) The "Update" and "Clear" buttons remain on the Full Command worksheet...
    (Note 7) Have a think about whether or not you want to retain the facility of being able to arrange names on the Full Command worksheet in a different order from that on the Department worksheets. Obviously this means that the names must be entered on the Full Command worksheet in the required order rather than just being "grabbed in bulk" from the department worksheets.

    Hello Again Greg,

    Let me see if I can answer / point out some things. In the quote above I have abbreviated your post to save room, each line is designated by a note number and I will use this number to respond.

    (Response 1) The Cell A1 issue still persists, I understand it now and will be able to correct it, the problem is thus... When you are retrieving data from a source, you are trying to output to a d mmm yyyy format in the cell this returns a value such as 1/1/2017 so because of cell formatting it looks like 1 Jan 2017, however and this is the catch, excel will always recognize a date value in a cell as mm/dd/yyyy regardless of how you have it formatted for the output view. If a cell contains a value of 1/1/2017 it is the first of January, but if it contains 1/2/2017 that is the second of January. In your code you have it pull data from three cells and put them together to make a full date but what you intend to be the first of February returns a value to the cell of 1/2/2017 which excel then interprets to be the second of January. The fix for this is not too involved in your code you just need to change the order to match what excel natively understands.

    (Response 2 & 3 & 6) These are awesome, I cannot wait for all you have in store for the Specify Absence userform, currently though I do need to note that on this userform the month block on both the start and stop dates is showing only 12 Januarys, I assume this is also related to the formatting of dates as listed in the above response. I had in your previous version moved the Update and Clear buttons to a similar location, this is great.

    (Response 4) Nope, no need to list out the duties and activities, I can drop a Key somewhere and explain it to people.

    (Response 7)
    I do like the idea of being able to sort the Full Command independently of the department sheets, reason being I will sort the departmental sheets alphabetically by name but when those are copied into the Full Command I will normally want them by Name but sometimes by rank, so yes please keep this independent. Additionally If I can get it (and I will work on this myself trying to use your code as a base) due to this desire to sort independently I would like a second button to fetch data to the Full Command sheet for names and ranks. I think I can figure this out as it should just be a matter of copying all data from each sheet in column a starting on row 4 through until it hits an empty cell, then do the same for each additional departmental sheet. I would like the button but it is not a deal breaker, if I have to I can manually copy all the names and ranks from each sheet and then sort it afterwards.

    Now that you have done so much work and we are nearing the "Finish Line" there are two additional small modifications I would like to know if they are possible.

    The first I believe will be simple and is more of a question, once the UserForm is used and a "bar" has been created, if this absence needs to be modified will there be any way to edit it? I picture that for easy modifications like the absence scheduled gets shifted to a week or so later we can just drag and drop the "bar" and if it needs completely overhauled just delete the bar and enter a new absence through the UserForm.

    The second is a bit harder possibly, I am wondering if there will be a way to filter on a grand scale. What I would really like to be able to do is this... Say we have a full year showing on the Full Command sheet, is there any way that I could filter this to show only those people with a certain absence, like say those that will attend a particular school during the year period. I have no idea how to do a filter across all cells in a range using a text based criteria but it does not sound like something that excel cannot do.

    Wow that's a long post, and I have to say again your work is FANTASTIC, Thank You so much.
    Ken
    Last edited by parkins5322; 01-31-2017 at 05:46 PM. Reason: formatting

  25. #25
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg M,
    Just a quick update, I have not had a whole lot of time to work with your new version but I know that I have not been able to figure out how to correct the months in the drop down on the UserForm for adding an absence. Additionally when I fix the date headers then the button for the visible months stops working, I am still looking into this.

    Ken

  26. #26
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hello Again Greg,

    Ok so I left work and made it home and really started digging into what you have here, looks like all the minor issues I talked about in previous posts I have been able to sort out, I will post the code snippets below. Three things I have corrected are the dropdown boxes on the Specify Absence UserForm (the month dropdowns showed 12 January's), Month Headers were only showing Januarys, and when fixing the Month Headers it broke the viewable range button. Here are my corrections, I left your original code in for all of them but added a '&&&&' in front of them to mark them as notes and make them easy for me to find if I needed to revert back to the originals.

    Specify Absence UserForm Code
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Month Headers (the previous problem with the A1 Cell entry)
    Please Login or Register  to view this content.
    While this one is on the FormatCalendar page it relates to the iMonthNo which directly affects the viewable month range buttons.
    Please Login or Register  to view this content.
    I hope yo agree with my corrections, they seem to be working fine on this end, let me know if there is anything else I can do from here.

    Ken

  27. #27
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi again Ken,

    Many thanks for all of your very detailed messages.

    Could you please test the attached version of the workbook at your end?

    I'm trying to solve the "real" issue of the different date formats, but in the absence of a solution it seems that your proposal should be fine.

    On the Jotter sheet there's a new cell with a blue background - this cell contains the DateValue of the starting date - the date constructed from the contents of the Day, Month (name, not number) and Year cells. This numerical value should be the same regardless of what date format is used, and it is this numerical value that is passed to the code.

    Please try it and let me know what happens.

    I'm working on an "erase" button for absences/duties that need to be deleted. At this stage it will probably be easier to delete an existing entry and then enter the new value, rather than trying to modify the existing entry.

    Please keep me posted!

    Regards,

    Greg M
    Attached Files Attached Files

  28. #28
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    It is doing the same thing again, I see your blue box and know what a date value is so at least we are working on the same page. This actually makes it really easy for me to show the problem. Ok so if you look at all your monthly headers, in each cell that has data for the header (so the first cell for the month) change this format to text, this will show the date value in the headers. Now if your version is acting like mine, when you update the calendar to say start in January of 2017 on the jotter, then go to the full command sheet you should see that all the headers are one single digit apart, meaning it was adding a single day instead of a month. By changing the vba code on the back end this corrects itself.

    Hope this helps a little more
    Cheers
    Ken

  29. #29
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Could you please check just the "Specify Absence" UserForm and see if the values displayed in the Month dropdown lists are correct?

    Regards,

    Greg M
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    Sorry for the delay, not sure if it was on my end on the forums but I kept getting a database error and could not look at the forums. Looking at your latest version yep the months in the userform dropdowns appear correct.

    Ken

  31. #31
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Yes, database errors etc. at this end also

    Great! Thanks for that very prompt feedback - I think I may be making some progress at last

    Latest version as soon as it's available!

    Regards & thanks,

    Greg M

  32. #32
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37
    Quote Originally Posted by Greg M View Post
    Hi Ken,

    Yes, database errors etc. at this end also

    Great! Thanks for that very prompt feedback - I think I may be making some progress at last

    Latest version as soon as it's available!

    Regards & thanks,

    Greg M
    Your quite welcome, is there anything else i can do on my end to help you??

    Ken

  33. #33
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Can you check out the Calendar formatting and the Select Period to Display features in the attached workbook and let me know if it works at your end? Thanks.

    Regards,

    Greg M
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    Yes the calendar headers all seem correct now, as does the functionality of the select period to display.

    anything else I can do to help?? Also do you think the two things I mentioned at the bottom of my post #24 are doable? I can deal with the first one if thats too much trouble, the second one I am not even sure if it is possible. It does not seem like it is out of the range of what excel should be able to do I just dont have a clue how to do it, I am attempting to search through google for something similar but so far I am drawing blanks.

    Thanks Again
    Ken

  35. #35
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Many thanks for that - who'd have thought that different date formats could have caused so much difficulty?!?!?!?!?

    I've made some progress on inserting/removing "bars" on the Department worksheets, so I hope to be able to send you something else tomorrow (busy day for me!) or Thursday.

    I'll have a look at the other items you mentioned when I get a chance.

    Best regards,

    Greg M

  36. #36
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Thank You Greg,

    One good thing about busy days, it makes the time go by quickly. Hopefully you get a nice uneventful but productive and with luck a somewhat easy day tomorrow, there is not immediate rush you are helping me after all, I would like to be able to have a working version in time for me to get it updated and use it in my weekly brief on tuesday, if that doesnt happen, not a horrible loss, there will be another meeting the following week haha.

    Ken

  37. #37
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Latest version attached.

    The "Add Absence" feature has been fully implemented on the EXEC worksheet - i.e. a "bar" will be inserted across the appropriate cells when the OK button on the UserForm has been clicked. Conditional Formatting has been removed from this worksheet, and the colour of the "bar" is as per the table on the Jotter worksheet. You can change these colours to suit your own requirements.

    The "Clear Selected Entry" feature has also been implemented - just select the first cell (the one that contains the text description) and then click on the "Clear Selected Entry" button.

    For the moment, I suggest that you DON'T use these features on any worksheet other than the EXEC worksheet.

    That's all for the moment - more as it develops!

    Regards,

    Greg M
    Attached Files Attached Files

  38. #38
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi again Ken,

    Latest version attached.

    The "Add Absence" form has been modified to make it an "Add Absence/Activity" form. The list of Activities and their associated background colours is given on the Jotter worksheet in exactly the same way as the list of Absences.

    The Add and Clear features are now operational on all Department worksheets.

    Play around with the attached workbook and let me know what you think.

    A suggestion for further enhancement would be to have a few Name sequences listed on the Jotter worksheet (e.g. alphabetical order, rank order, department order etc.) and to have a button to transfer the appropriate list to the Full Command worksheet. The data for the various individuals would then be transferred to the Full Command worksheet by using the "Update Sheet" button as normal.


    Regards,

    Greg M
    Attached Files Attached Files

  39. #39
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hello again Greg,

    Man you are a wizard at this stuff. I am not sure how much work was involved in altering the Add Absence / Activity but unfortunately it was un-needed, all of the cells with data in the previously were Schools, Report to Ship dates, or Temporarily Assignments (TAD), the previous Add Absence form worked well since you had both the code and the description so my use would be like this...

    Absence Type - School Description Pollution Abatement the "bar" would then be created saying School - Pollution Abatement which works perfect for me. I do love the color and polished design of the new version quite a lot though.

    If making modifications to this form, I have been trying to look at your last version and see what you have triggering the form to popup, I was wondering (now don't get me wrong I love how it works right now, and if it will cause lots of extra work, latency or any other issues then by all means disregard the following statement) if it could also be linked to all the cells where the bars would appear and auto fill the start date based on the cell that was selected. The more I think about this now we are getting into dates again and this may be more work than it is worth, just something I was tossing around.

    Absolutely love the suggestion for the information on the Jotter sheet to sort by.

    The last three things I am fiddling with is I am attempting to copy your "Select Period to Display" button from the Full Command sheet into the departmental sheets. Second I am seeing how difficult it would be to modify the clear button so that instead of only selecting the first cell that has data I could select say a whole month and delete them all at once. Normally I would not think that this would be very useful but I have come up with two scenarios where I see a use for it. The first and probably the main reason I may need this functionality is that we can get ordered to sea at a moments notice and everything else gets shifted, so if Monday morning I come to work and find out there was an earthquake in Haiti and because of this we are leaving Friday and will be gone for a month, all schools, appointments, leave etc will all have to be rescheduled, easiest solution for the tracker, delete the months worth of "bars". The only other thing I can come up for for needing this would be the start of a new year, unless I save a blank copy and make it a Master Template and hope that nothing ever happens to it, if you change the year range on the jotter it changes the dates but leaves all the "bars" in place, a quick select all cells with data click of the mouse and bam, new year ready for editing. I tried selecting the cells and deleting them with the delete key but since the bar colors are now hard coded instead of using conditional formatting the color sticks around. Just going over things that would be nice though, this is by no means required, I can still select all cells with data, delete this manually, then go to cell formatting and choose no fill to get rid of the colors, just trying to simplify things as much as possible because if you have ever worked with a 25 year old that has a degree in Foreign Politics from 1600 - 1892 you would know exactly why I am trying to stupid proof this haha. Ok so last thing that I am working on and still have not made headway on is the ability to filter the sheets by absence. I know how to do normal filtering but it always filters by data in a single column, we are working with 372 columns so not sure how to accomplish this. My current attempt is to have a hidden cell that concatenates data from all cells in the row then filters based on a "portion" of that concatenation. Like I said not making any headway just yet but I think I am headed in the right direction.

    Have I mentioned how much I love your work lately, truly masterful.

    Thank You, and I will Keep playing around and let you know what I come up with.
    Ken

  40. #40
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Just a quick reply to one of the points you raised, i.e. not needing the "Enter Absence" UserForm to be able to cope with entering Activities also. I can do one of two things - re-install the original single-purpose UserForm, or keep the dual-purpose UserForm but set the default option to "Enter Absence". The latter approach keeps the facility available for use if is ever needed in the future. Neither option is too difficult to implement.

    I'll think about what I might be able to do as regards providing a "Bulk Clear" facility. As is often the case, the difficulty is as much in finding an appropriate location for the button as it is for determining the code involved. One possibility would be to move all of the control buttons from the worksheets to a new "Year-Long Schedule" tab on the Ribbon itself.

    I haven't thought it through yet, but it shouldn't be too difficult to extend the "Select Period to Display" feature to the Department worksheets.

    I'll keep you posted.

    Regards,

    Greg M

  41. #41
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    Ok so I have been knocking out as much as I can on my end and I think we are within spitting distance of the finish line. Thanks again for the MASSIVE amount of work you have put into this project for me.

    I agree, I think keeping the functionality of the Enter Absence UserForm is probably a good thing, so yes lets keep that the way it is, like you say later if we decide we never use that side of it I can copy the original version from one of the previous versions of the file.

    I was able to create a button for a bulk clear that leaves the format of the cells intact, so I have added this to each page as well, I put the code for this button directly below the code you have to clear the Full Command sheet. I know that this is probably not the best location but I liked having them near one another. I was also able to modify your code for the Select Months button so that it will work on whichever sheet is active, then I copied those buttons to each of the departmental sheets as well. Finally I was working towards the ability to filter across all columns, here is my solution.

    In cell NE2 there is a formula (I deactivated the formula by putting a space in front of the =) this formula will concatenate all the values from each cell along a row in the "calendar" field. Once this formula has been copied to row 4, drag the formula to the bottom of the used rows. Next I have to select cells NE4 and NE5, then click filter, a dropdown arrow will appear in cell NE4, I click this and from the menu select text filters, select contains, then in the popup window enter my value I want to filter by. This works for me but I am trying to make a userform and a button to accomplish all of this in one shot, so far not going well but this is not a deal breaker.

    As I have the workbook saved it is functional and can be used as is, I am just still kicking around a few ideas as well as that filter thing.

    Ok that's my update, Thanks again Greg.

    Ken
    Attached Files Attached Files

  42. #42
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Many thanks for your latest post and for your private messages.

    Take a look at the attached version and see what you think.

    The first thing to notice is that all of the control buttons (except for the Update Calendars button which should be less accessible anyway) have been moved to a Year Planner tab on the Ribbon.

    You now have two Select Period To Display buttons - one of which displays the required period on the selected worksheet, and one which displays the same required period on all worksheets.

    I've included a Bulk Delete feature which is a bit less "violent" than your version This feature allows you to specify a Start date and an End date for the period from which the entries should be deleted. If an entry "overflows" into the period to be deleted (either at the start or at the end of the period), the entire entry is deleted.

    As in the case of the Select Period To Display buttons, there are two Bulk Delete buttons - one operates on the selected Department worksheet only, and the other operates on all Department worksheets. A "Confirm Operation" warning message is displayed before any data are deleted.

    I've retained the dual-function Enter Absence/Activity UserForm, but the default display will always be Enter Absence.

    I'm thinking about how to implement the different orders for Names on the Full Command worksheet, and hope to have something ready fairly soon.

    Haven't had a chance to study your filtering feature yet, but will do that as soon as I get a chance.

    Keep me posted!

    Regards,

    Greg M
    Attached Files Attached Files

  43. #43
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    I've added the facility for storing Names in different sequences on the Jotter worksheet, and then transferring them automatically to the Full Command worksheet. I used a random name generator to produce a list of employee names so that the application looks a bit more realistic while it's being developed.

    Three Name sequences are provided: by Name, by Department and by Rank (listed in alphabetical order, as I obviously don't know if a CSSN is junior/senior to a SHSN). Just click on the appropriate button on the Jotter worksheet and the Full Command worksheet will be updated accordingly.

    I'm thinking about whether or not it will be possible to implement your suggestion for selecting a date cell and then automatically populating the Enter Absence UserForm with that date - I'll see what I can do.

    You always find that the more you work with things like this, the more "bright ideas" keep coming your way!

    Regards,

    Greg M
    Attached Files Attached Files

  44. #44
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    The latest version of the workbook is attached.

    I've had a look at your filtering feature and have come up with a possible alternative based on "basic" absence categories - i.e. the filter will regard Sch - abc, Sch - def and Sch - ghi as all falling within the Sch category.

    The filter is initially driven from a button on the Ribbon, but filters can also be applied "manually" in Cells NI2 - NT2.

    Hope this helps - see what you think.

    Regards,

    Greg M
    Attached Files Attached Files

  45. #45
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi again Ken.

    After this one, I think I'm fresh out of ideas!

    One of the items on your earlier wish list was a facility which would allow you to right-click on a date cell and have the UserForm displayed with that date pre-populated on it. The attached version includes this feature.

    As always, please keep me posted.

    Regards,

    Greg M
    Attached Files Attached Files

  46. #46
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Greg,

    Hello, I have not dropped off the face of the planet, been a rough couple days of work, hopefully I will be able to work on this tomorrow some and I will let you know what it looks like on my end. I did get about 10 minutes at work today to try out your download from post number 44 and 45, not sure what the issue was but your new tab did not appear for me, came home and they both show up fine on my personal computer, not sure if it is some restriction or what on my work computer I will let you know more as I find out more.

    Thanks again and hopefully I will be able to post more tomorrow

    Ken

  47. #47
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hello Again Greg,

    As I mentioned in my last post I am unsure why, when I use a Navy computer that the Ribbon Tab that you created does not appear, I may have to go in to the office today for other matters and maybe I can look at this while I am there and decipher that little problem. I understand you are using an xml file to generate this ribbon but cannot see why it would not appear on those computers but works fine on mine. I have an updated version of the tracker we were using with all the correct data in it and my goal is this weekend to get it all into the new sheet that you and I have been creating so I can use it for my meeting first thing Tuesday Morning.

    From the brief 5 minutes I have been able to play with this so far this morning I have some pros and cons to the new three sheets using the ribbon. So far all of the functionality seems to work well, I am unsure of how your sorting feature is working on the backend (have not really looked into it yet) but my intention is to hide the jotter sheet so that people that dont need to use it cant screw anything up. From what I can tell you have to list all of the names manually as they do not update based on names entered into any of the other sheets. Once I figure out how you have the sort feature working I may see if I can automate the lists so that I can hide the sheet and it will auto update, we will see. I may even try my hand at moving them off the jotter sheet and into the ribbon bar beside the other buttons for the full command sheet, of course this all depends on how the macro / code is written.

    I am begining to really like the custom ribbon bar, I used excel for far too long without the ribbon for me to like using it right off the bat haha (yep Im old, well older anyway) but I do really like having all the buttons in one place and out of the way of the data sheet. I may play with the icons and labels a little (once I remember my xml). Biggest thing I would do with that would be to make them all large icons and change the labels to something closer to Navy Jargon. Currently I have been opening the .xlsm with 7zip to see all the files and then have opened the xml with notepad++, do you recommend something else to make this easier? I did see a free program out there specifically for xml in excel but have not tried it. I know it can be done in visual studio as well but dont even know how to open the file system in that program.

    Next up is the filtering system, I like what you have done so far, but it simply doesnt quite work for me, I may come bearing at least part of a solution though, see what you think. A little background information appears to be in order, absences such as Leave (LV, time off that counts against a balance of accrued days, normally more than one day in length at a time), Liberty (LIB, time off that does not count against the leave balance, normally only one to three days), Sick in Quarters (SIQ, time off based on medical order, normally a single day but up to three days), Report to Ship (RTS, date that member will arrive to ship and become member of ships crew, single date), Appointment (APT, a pre-scheduled appointment, normally single date) are generalized absences and we will never need to filter them based on the descriptions so these work with your current filtering system perfectly fine. The remaining absences have specific descriptions attached that we have need to filter based upon. These are Schools (SCH, an assigned school, schools can be anywhere from a single day to six months in length), Temporarily Assigned Duty (TAD, this is when a Sailor is sent to another command / facility to work temporarily, typical TAD times range between a day and two months though in extreme cases can be up to a year), and finally Underway (UW, This absence will only be used when our Sailors are sent to another command to go out to sea for a short period, typical absences will range between three days and one month). Ok now that we understand each of the absences a little better, like I said above the first five types of absences will work fine in your filtering system, the last three however we will be using the description of absence to filter. As an example, if we have 19 Sailors that will attend the Pollution Abatement school this year, I will have the need to filter not just all Sailors that have school some time in the year but specifically which Sailors have the Pollution Abatement school throughout the year. This is why I had to use the concatenate formula and the text based filtering. Now in my mind to merge your beautiful work with my ungainly but marginally effective work would be to continue to use your "table" on cells NI through NP of the Full Command Sheet, from this we could remove the columns for SCH, TAD, and UW and replace all three with the concatenated formula I was using. Now the tricky part, on the select filter values userform you have created remove the same three but add a radio button for "based on text value" and have a text box that would search the column with the concatenated data.

    The add absence based on a right click in a cell that has a name value in the first column works beautifully, I love it. If I had to change it, and by no means is this necessary, I would modify it slightly so I could select a range and both the Start and stop dates would autofill. If thats not doable thats also fine, like I said I love it the way it is.

    I am trying not to have any more "bright ideas" as I think you have done way way above and beyond my wildest imaginations of what this sheet could be and I am sure you are getting close to just wanting to be done with it as well. So that was my "quick" review after just starting to work with it. I look forward to seeing a finished product as I am sure you are as well.

    Thank you again, so much of this is only realized because of the massive ammount of work you have put into this project.
    Ken

  48. #48
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    This one should be quick...

    Quote Originally Posted by Greg M View Post
    Three Name sequences are provided: by Name, by Department and by Rank (listed in alphabetical order, as I obviously don't know if a CSSN is junior/senior to a SHSN).
    I know that excel allows for custom sort orders based upon a list that is manually filled in, for the rank in particular if I generate a list of how I would want them sorted is it possible to use this list and create a custom sort with the button you already have?

    Ken

    EDIT: Ok I see how you have done it, you sort the list on the jotter sheet then click the button and it will autofill into the fullcommand sheet. I attempted a custom sort list but the number of rate and ranks that we have exceeds the 255 character limit of a custom sort list so I am trying to find a workaround. I know that we wont be using many of these rate / rank combinations but I am trying to build it in for future changes. My current list is below, and I found out you cannot use wildcards in a custom list so still working the issue...

    Please Login or Register  to view this content.
    Last edited by parkins5322; 02-04-2017 at 11:21 AM.

  49. #49
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    Ok so I have had the majority of the day to play around with this and have done quite a bit of learning and work, here are my results...

    My intention is still to automate the name lists on the jotter page, but have not gotten that far just yet, what I have accomplished is to copy your macro for the sort buttons, unfortunately I saw no way to do like yours and only have the one macro, so I made three copies and designated them for the buttons I created on the ribbon bar.

    I managed to rename, move, change the icon and otherwise sort the custom ribbon bar.

    I know it does not sound like much but it has taken me all day to get this done haha. I have not gotten around to working with the filtering issues I mentioned in post 47 but I did find a work around for the issues in post 48. For this I ended up putting in a column that shows our rank structure then creating a helper column that assigns a numeric value using the match function, then sort this column least to greatest and everything is as it should be.

    I have attached a copy of my sheet so you can see how I have been muddling with your code. I am really happy overall with the total product, just need to figure out that filtering part and I think I will be good to go.

    Thanks so much for listening to me ramble and for all your hard work

    Ken
    Attached Files Attached Files

  50. #50
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    Ok so I have had the majority of the day to play around with this and have done quite a bit of learning and work, here are my results...

    My intention is still to automate the name lists on the jotter page, but have not gotten that far just yet, what I have accomplished is to copy your macro for the sort buttons, unfortunately I saw no way to do like yours and only have the one macro, so I made three copies and designated them for the buttons I created on the ribbon bar.

    I managed to rename, move, change the icon and otherwise sort the custom ribbon bar.

    I know it does not sound like much but it has taken me all day to get this done haha. I have not gotten around to working with the filtering issues I mentioned in post 47 but I did find a work around for the issues in post 48. For this I ended up putting in a column that shows our rank structure then creating a helper column that assigns a numeric value using the match function, then sort this column least to greatest and everything is as it should be.

    I have attached a copy of my sheet so you can see how I have been muddling with your code. I am really happy overall with the total product, just need to figure out that filtering part and I think I will be good to go.

    Thanks so much for listening to me ramble and for all your hard work

    Ken

  51. #51
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    Ok so I have had the majority of the day to play around with this and have done quite a bit of learning and work, here are my results...

    My intention is still to automate the name lists on the jotter page, but have not gotten that far just yet, what I have accomplished is to copy your macro for the sort buttons, unfortunately I saw no way to do like yours and only have the one macro, so I made three copies and designated them for the buttons I created on the ribbon bar.

    I managed to rename, move, change the icon and otherwise sort the custom ribbon bar.

    I know it does not sound like much but it has taken me all day to get this done haha. I have not gotten around to working with the filtering issues I mentioned in post 47 but I did find a work around for the issues in post 48. For this I ended up putting in a column that shows our rank structure then creating a helper column that assigns a numeric value using the match function, then sort this column least to greatest and everything is as it should be.

    I have attached a copy of my sheet so you can see how I have been muddling with your code. I am really happy overall with the total product, just need to figure out that filtering part and I think I will be good to go.

    Thanks so much for listening to me ramble and for all your hard work

    Ken
    Attached Files Attached Files
    Last edited by parkins5322; 02-04-2017 at 08:22 PM.

  52. #52
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Many thanks for all of your feedback.

    I'll have a look and see what I can do, but it might take a day or so - a bit busy at this end!

    Regards,

    Greg M

  53. #53
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hello Again Greg,

    So I think we may be done. I have made the last of the modifications and gotten them working. I am quite happy with the product and will attempt to add all of my real world data to it today and tomorrow so I can brief my Commanding Officer using it on Tuesday. I changed button labels a little more and moved things around, got the formatting working mostly. I could not figure out your code other than how the radio buttons are wired so instead of messing it up I made a multi function macro that does two things, 1 it concatenates a single row from column c to column nc, then 2 it opens a dialog box for you to enter your text to filter by, then it filters based on the concatenation value containing your text. I did not make a pretty form for the dialog box, instead I just used the built in popup that is native to Excel. Some other minor tweaks here and there but overall I like it a lot.

    Let me know what you think and if you have other better ideas to accomplish what I have.

    Ken
    Attached Files Attached Files

  54. #54
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi again Ken,

    Many thanks for all of your feedback, comments etc.

    I think this project has achieved significantly more than the initial expectations, and that we can both feel justifiably pleased with what has been accomplished to date!

    In the attached workbook I've addressed the situation regarding the sorting of names.

    The definitive names and sequences are assumed to be those listed in the various Department worksheets. The Jotter worksheet contains a "Retrieve Names" button which I've deliberately kept away from the Ribbon, as I think it should be used only by Users "in the know". This button collects the names & ranks from the various department worksheets, and copies them three times to the Jotter worksheet. The first set of copied names & ranks also contains Department headers, the second set of copied data is sorted on the basis of name, and the third set is sorted on the basis of Rank Number. The Rank Number is initially entered as a formula, and then each formula is replaced with its calculated value - this is important, because sorting data on the basis of a key which contains a formula can produce results which are at best unpredictable!

    The code involved includes the following:
    Please Login or Register  to view this content.
    To include more/fewer separating lines between departments on the Full Command worksheet, just change this value,

    To change the sequence in which Departments are listed on the Full Command worksheet, just change this sequence to whatever is required.


    I'll have a look at the filtering requirements for the Sch, Tad and Uw absence categories and see if I can suggest any further improvements.


    Regarding your question about editing the Ribbon XML, the program I use is the free Microsoft Office Ribbon Editor.


    Hope all of the above helps. Your feedback and comments are both interesting and useful, so please keep them coming.

    Regards,

    Greg M
    Attached Files Attached Files

  55. #55
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hello again Greg,

    I dont work tomorrow so wont be able to test it, but the last several versions of this file I have attempted to open since you added the custom ribbon bar, the file opens fine on my computer at home but when it opens at work the ribbon bar does not appear, do you happen to have any idea what would cause this?? I am pretty sure it is office 2013 but it might be 2010. At home I am running 16 but from what I have been able to find online the custom ribbon bar has been implemented into excel since 07. My worst fear is that we have done all this work on the ribbon bar and wont be able to use it where we designed it to work.

    Thanks Again,

    Ken

  56. #56
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    The attached workbook has taken a new approach to the issue of filtering on the basis of Absence Categories - have a look and see what you think of it.

    It also includes a feature you requested previously, i.e. selecting multiple contiguous cells (in a single row) on a department worksheet and then right-clicking on the selected cells will display the Enter Absence UserForm pre-populated with the appropriate Begin and End dates.

    Regarding the difficulty in displaying the Ribbon when running on your work computer, is there any possibility that the work computer is running Office 2007? Office 2007 uses a different XML format from versions 2010 and later. Let me know if this is the case - if so, I don't think it will be too difficult to convert the current XML to the 2007 format.

    Regards,

    Greg M
    Attached Files Attached Files

  57. #57
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    The modifications sound awesome I will check them out tomorrow night, today I am still working on the network issue. At the office we are running windows 10 x64, same at home, at work Office 2013 at home 2016. Both 13 and 16 use the same schema and CustomUI14 file so I am not sure what is going on. Originally I thought maybe permissions or some such, but I have the ability to run macros and took pictures of all the "trust" settings and they match what I am running at the house. At this point I am at a loss, nothing I have been able to find anywhere talking about custom ribbon bars, custom IRibbons, or customUI has resulted in a solution to why it would not work on a networked computer. I did stumble across one article with the same problem but there were no solutions listed and the article from several years ago. I am so frustrated because the last version that I was capable of using at work, which is the only place it will ever be used, was your version prior to it having the ribbon. Let me know what you think, and Ill look at your latest version.

    Ken

  58. #58
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi again Ken,

    Thanks for your private message.

    Sorry I can't suggest anything else to overcome the problem of the non-appearing Ribbon - maybe you'll have better luck now that you've raised that issue as a separate post.

    I forgot to mention in my last post that the Enter Absence/Activity UserForm has been tweaked so that it once again looks like the original Enter Absence UserForm. Should the dual functionality be required in the future, all that is necessary is to enter some Activity types in the Activity Codes table on the Jotter worksheet, and the UserForm will automatically configure itself to provide the dual functionality.

    Regards,

    Greg M

  59. #59
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi again Greg,

    Quite welcome, I assumed that with the PM you would get a notice faster than if I posted a reply. I am going to continue to play with it tomorrow, I burned copies of all the files I have since the ribbon was implemented to a disk and will have it in the office tomorrow. I still think it is probably some issue with permissions. Hopefully the IT will be there tomorrow and will grant me the access that will be required to show the ribbon. I really really dont want to have to revert back to prior to the ribbon, trying to put everything into buttons and finding a place to put the buttons does not sound like a good time to me.

    Thanks for the help again and I will look at what you have done in the new sheet, nearly time for bed now though, 4am wake up comes awefully early.

    Ken

  60. #60
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Greg,

    Dude that filtering and the selection of multiple cells, man thats awesome. Now I just need to be able to use the file at work and we are golden.

    Thanks Again for all your hard work, this is the best excel sheet I have ever seen.

    Ken.

  61. #61
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Just a small cosmetic change to the UserForm - it provides different background colours for the two pages on the Multipage control.

    Keep me posted on any developments regarding the non-appearance of the Ribbon when running on your work computer.

    Regards,

    Greg M
    Attached Files Attached Files

  62. #62
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    This is a test post.

    After my last post (#61), the thread tab updated to say that there was now a Page 5 in this thread, but clicking on Tab 5 just takes me to Page 4

    Maybe including another post will cause Page 5 to increase enough to be noticed by the system???

    Regards,

    Greg M



    P. S. Yes - that worked!!!
    Last edited by Greg M; 02-07-2017 at 07:37 AM. Reason: P. S. added

  63. #63
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Haha,

    Hello Greg...

    So the long and short of the missing ribbon bar issue is this, we in the Department of Defense use a Security Technical Implementation Guides, unfortunately we were using a version of this that specifically prevents the use of custom UI. Like any good person that does not want to throw away three weeks worth of work, I dug a little deeper instead of just agreeing with my civilian IT. Turns out that since the STIG that we are using came out they have updated it twice, the first updat... drumroll please... The only thing this update does is to remove the banning of custom UIs. I am now in negotiations with the IT department to get them to update their STIG to match the DOD's most recent version and to give me access to the full functionality of this file. We will see how this goes. Tonight I get to play with your most recent modifications.

    Ken

  64. #64
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    Many thanks for that - with luck we're nearing the end of the "Ribbon problem" road!

    As always, I'll be glad to get your comments/feedback on the latest version.

    Regards,

    Greg M

  65. #65
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi Ken,

    I was fairly pleased with the filter feature of the last version, so I decided to tweak it a bit further

    The attached version lists the current filter selections (if any) in Cell NF1 of the Full Command worksheet, and those selections are also displayed (ticked/selected) when the Select Filter UserForm is displayed.

    Take a look and see what you think.

    Regards,

    Greg M
    Attached Files Attached Files

  66. #66
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Year Long Schedule

    Hi Greg,

    Sorry that I have not been able to post, I have been out to sea. Good news is that I took the file with me and my command has been using it for almost a week now and it is going great. As easy as we have made the file training still seems to be an issue haha. Several of the people that are using the file have had several explanations on how to use it, a couple are even starting to get it.

    I want to thank you again for all the hard work you have put into making this project a reality and for the truly massive amount of work that had to be accomplished to create this amazing product.

    Sincerely
    Ken

  67. #67
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Year Long Schedule

    Hi again Ken,

    Many thanks for your latest feedback and also for your private message.

    Feedback like this is what keeps me coming back to the forum

    Delighted to hear that everything seems to be working correctly now and that the application is being used successfully by you and your colleagues. It's been an interesting and rewarding project, and your feedback and comments have been most useful throughout it.

    Please feel free to contact me if you think there's anything further I can help you with.

    Best regards,

    Greg M

+ 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. Replies: 5
    Last Post: 01-05-2016, 06:35 PM
  2. Transpose year long hourly time-series
    By fidu_k in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-29-2014, 01:57 PM
  3. Replies: 8
    Last Post: 07-15-2013, 07:13 PM
  4. Replies: 1
    Last Post: 05-22-2011, 12:47 PM
  5. How to do amortization schedule with 30/360 year
    By Dennis_ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-04-2008, 09:21 PM
  6. [SOLVED] maturity schedule fixed inc securities by month, quarter and year
    By maturity schedule in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2006, 07:10 PM
  7. Loan amortization schedule should have subtotals for each year
    By Lianyi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2006, 08:35 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1