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
Bookmarks