How To Create This AMAZING Week View Schedule In Excel [Part 1]

hello this is Randy with Excel for freelancers and thank you for joining me this week we will have a week view scheduler for you today we will show you how to create and schedule appointments very very easily you can also have the ability to change the color of any appointments very quickly simply by changing a format in a specified cell just like this and then go ahead and refresh the screen it's going to be a great training I look forward to getting started with you so thank you alright thank you so much for joining me today we have a week view scheduler as I said we do have the ability to do previous week this week we can select the calendar and go change specific date or next week in this scheduling training now if you remember we did have an appointment scheduler before and it was more of a month view and we did this a few months back we had the ability to schedule specific meetings on specific days and go to any month as well as email and use multiple staff however in this training it's gonna be a little bit different one we do have a week view here but in this particular training we also have the ability to schedule a duration and we can then set any specific duration on this schedule from anywhere from as little as fifteen minutes all the way on up to eight hours so this particular schedule is a lot more flexible in that manner as far as booking out specific blocks of time and we do also have the ability to set a specific start date so if your start date is on Monday you can go ahead and set it to Monday and the schedule will automatically refresh on Monday and we also have the ability to set start times as well so if our start time is at let's say 7:00 a.m. the schedule will go ahead and update and darken out we do have the ability to schedule any appointments from 12:00 a.m. until 11:45 p.m. however your window of time that is when white is based on the start and end times that you set here so we have a start time and we have an end time so let's go ahead and go back to 6:00 a.m. the way we had it and we also have set time of end time of 7:00 p.m. so we have a lot of flexibility there as well in that alert go ahead and set that correctly not 6:00 p.m. we want 6:00 a.m. we need more than one hour of working at least I do I don't know about you so let's go ahead and set this to 6:00 a.m. now we also have the ability to select a specific day in a calendar as well as change the color as I mentioned before so if we go ahead and set simply by changing the background of the filled color we can also change all the appointment colors so let's go ahead and go to this turquoise and then we can just update the schedule we can click on this week and you'll also notice that automatically Scrolls to the start time so that is a feature so automatically is gonna scroll to 6:00 a.m. which is generally our start time so that's really really a helpful feature for the user we can click on the button open up a calendar and specific select a specific week or use the next week and previous week buttons to navigate any particular week so it's a super powerful scheduler and yet it's very simple to create so I really wanted to show this to you let's zoom out a little bit so you can see the idea of it you can increase or decrease column size - based on your appointments let's go ahead and decrease it a little bit so you get more of a picture of the full screen we can't do this simply by highlighting the days in here right now we have it at 15 we can bring that to perhaps let's say a 12 so you can see more of this schedule and then we'll go in to call them with here and make that let's go to 13 we'll reduce it just a little bit so we don't offset anything okay great and so what we've done is we've created multiple buttons and let's go ahead and bring these buttons out a little bit you can also group these as well if you like so that they don't move if you plan on moving your columns a lot back and forth you may want to group all of these together and that can be really helpful when spacing if you don't want to space them out so we will just distribute them horizontally so they're nicely spread out all right so let's go ahead and go over the details and see how we went about creating this and how we went about doing this now there are some similarities to the month view if you remember that the scheduler we store our scheduling on a database and the database is based on the year and right now we have just 2018 however I want this particular scheduler application just like in the other one I want to create as many years as necessary so for example if we select a date within a different year right I want to make sure that the database updates and does it automatically so if we were to create a specific schedule let's say we create an appointment you see now we have now we automatically have 2019 that was not here a minute ago so we've done that dynamically through VBA and I'll show you how we did that as well so regardless of the year that we select if there's no particular schedule that's set on that it will automatically create one here we have 2023 we select a specific date and now you see year 2023 is there so and inside that we've based our database on the dates so here's how the here's how the functionality will work each particular database is made up of two particular cells for one date I want to store two items I want to store information about the duration and I want to store the details and then and that is it just those two items so we have two cells per date and we have extended this all the way to the end which is the last day and we've left one for leap year one for leap year so I believe in 2020 is a leap year so we've done this with a formula it's just gonna basically our formula says if this date if this date December 31st 2019 plus one plus one right here is the same year right is the same year as this in other words if these two if this if this Plus this r1 is the same year then it would be a leap year so ABC let's go ahead and create a schedule for 2020 so I can show you it's kind of important information so let's go ahead and do that I think 2020 is a leap here let's go ahead and pull that out and okay now we've created 2020 and here it is right here we're gonna go to the last particular and you see 2020 is a leap year and you see ABC right that is December 31st but whereas on the other years - excuse me 2023 ABC here is blank and remember that has to do it to leap year so what we've done is we've used a formula to add one more date in 2020 otherwise it's left blank so here's the formula if it says the year of ABA 1 equals the year of ABA plus one if they're the same in other words if the year of this and the year of this are the same if they're the same year then go ahead and put the date in here if they're not the same year then blank so if they're the same this and this the same then put a be a plus 1 otherwise leave it blank so that's how that works every single database year are all identical except for the starting date except for this when we create them we put in a different year here alright a different year here so that's how that's done we'll go over with the code on how we did that actually but I wanted to see you how that works as well we've also used two different conditional formatting as well let's go ahead and close that and that we've used let's go ahead and click this week and I wanted to show you two different types of conditional formatting on this just two we have using just two we're using one for the current date I want the current date today is April 29th the day that I'm recording this of 2018 and I want the current day to show up in orange that's one conditional formatting the other one said is if are working times if are working times or scheduled times are below this under right earlier I should say earlier than our storing time I want it to appear in gray likewise if they are over our end time if our end time here's where we set our start time is 6:00 a.m. our end time is 7:00 p.m. so that means that any time before 6:00 a.m. I want to make sure it's in gray any row that is 7:00 a.m. or over I want to make sure that that is in gray as well and take a notice here 7:00 p.m. 7:00 p.m. I should say it is actually grayed out so in actuality any time that equals 7 p.m. or over should be grayed out anytime under 6 a.m. not equal or under any time under 6 a.m. should also be grabbed we do that with a formula let's go ahead and go in to the conditional formatting home and conditional formatting and manager rules and we'll click on the entire sheet because I want you to see that we have just a few particular ones now these are once for every particular day let's go ahead and click on just the worksheet again and we can drill down now these are particular days let's go ahead and focus on this one we meet just one conditional format to take care of the non work time so let's go ahead and edit that rule and all I've used is a simple or rule so again start time we've used I've created a named range for that particular cell for this particular cell is start time and I've created a named range for the end time and it's called end time that way no matter where you move these two it'll always link up so basically what we're saying is d6 now d6 is our first row of our first row of times D is the column six here that is our first time and the critically important thing here make sure there's no dollar sign no dollar sign before the six okay that's critical because we want this rule to apply to every single row in our range so we want to make sure that there is no dollars and that's the critical rule here and the same thing for end time if if either one of these conditions are true then make it grade then we want to set the format we can't even do it you know darker gray if you want right and so we can apply that and you'll see it turns of darker gray so the idea is here that in or if d6 is less than the start time less than the start time make it great or if d6 is greater or equal greater or equal than the end time then also make it great so that's why when I say greater equal that's why 7:00 p.m. his hour end time but it's also equal to so that means that our last appointment we can schedule at 6:45 p.m. and that is set here 6:00 a.m. has been set as the start time and end time has been set and the named range end time has been set for a d5 so we've got those two named ranges there now let's go ahead and go over that so this is really simple we have here I've just set a named range of durations we want to give the user the ability to set any duration from 15 minutes all the way up to 8 o'clock ADA excuse me 8 hours 15 minutes up to 8 hours so I've set a duration now it says 12:15 a.m. here but in actuality this is 15 minutes so let's go ahead and see that we've given this a format of time but it's a it's a custom format in this sample here I've used 1330 and this particular format given given the correct number is actually 15 minutes so if we were to change the format to this to a number you would see what that number is and let's go ahead and change that to accounting that's actually point zero zero zero zero four six so let's increase that decimal right so you can see how we've formatted how that number let's go ahead and click back on the cell here's the number point zero zero one so this is actually fifteen minutes and why is that because one is that is a full day what about one hour well one hour is one day divided by 24 so how do we arrive at this number well we can use simple math for that okay we know one that day is 1 right so what is 1 hour would be 1 divided by 24 right 1 divided by 24 so if we do that math we're gonna get to that now that's point 0 well what about 15 minutes would be 1/4 of this correct one 1/4 of this divided into 4 again that's how you get your 15 minutes that is the 15 minutes so we're gonna place that right in there and then we give it the time format which is this right here 1313 30 here and that's how we get our 15 minutes then all we need to do is simply add 15 minutes add 15 minutes which we're actually going to be doing is adding this number to the 15 minutes so we do that for each one and we increase increase increase 50 minutes so remember the important thing with time is those are all decimals generally time is as a decimal less than one full day is what two days is two so that in Excel is how Excel treats times so we've named that range so if you click on this that named range is called duration and we've used that named range in a data validation for here and for every particular cell that includes the duration so under home as you me under data and data validation you see that that equals duration and I've done that for all seven days and this is an open text field where you can write in anything you want now let's go ahead and go over the conditional formatting for days and this is quite simple for each day of something separate so when we go into conditional formatting manage rules all I've done is said here if d 4 equals today d 4 is the cell in the upper left corner of this date if it's today then we're gonna format it and I've done the same thing for each and it applies to d 4 through F 5 so it applies to just these cells here and I've done the repeat of the same thing for every single day so the just the two conditional formats are very very simple for this particular training now I did not use conditional format it's for the times because I'm I wanted to make it super simple on the code so the idea is when we select 30 minutes I want two cells to fill in when we select 45 minutes I want 3 cells to fill and since they're 15 minute intervals it's really quite simple and then just the ability to type in any name you want and then automatically that information is being saved so when we go to previous week the schedule is clear when we go to next week automatically the information returns and that's because we're saving it in the database and it's not necessarily formatted very pretty but it doesn't have to be in fact I would recommend not to simply to save space and speed up the work but there is no reason so when we go to April 29th under 218 let's go to April 29th ok 420 here we go so here's the information you see it's not formatted and we don't need it to be we just need the data in there that's all we really want often most likely if you were to send this you would hide these sheets there's no reason really to have these sheets these sheets visible and most likely you'll want to hide them if you use these in applications for your co-workers or you hand them out to your customers or you're creating a larger application they're really simply to hold the data and recall the data and save the data but there's no so and the reason is now take a look at this now you know that on the schedule let's take for example Lisa Smith 45 minutes but if you look on the schedule it's 3 it doesn't see 45 minutes that's purposely that's purposely and the reason is is because when I load that schedule and I load that schedule I want to know right away how many rows to color I want to know how many rows to color in this case we're gonna color two rows three rows and seven rows in this case so 3 2 3 7 right so I want to know easily very quickly how many rows should I recolor so having these numbers here really help us 3 2 3 7 we know exactly so when I'm pulling that data back in I know exactly I mean now also when I but I don't want when I fill this schedule back up I don't want to show 3 2 3 here right I don't want that I want to show the actual times but how do I quickly get those times in there and not show the numbers well if you look closely at this in column a right and you go down 1 2 3 3 is 145 right 1 is 15 minutes and then 7 is 145 so if I know right if I know I need to choose the first value or the second value or in this case the third value I can quickly pull this right into here because I know I've got 3 right we have 3 from the database I have 3 so how do we get 45 well it's relatively simple we just know we need to go three down and get it and I'll show you exactly how we did that in the code as well so as well as the fill color I'll show you how we did that and the sunday and let's go ahead and do this the last feature that I want to show you that's non code is this feature here where we can select the starting day and in this case we do that with a simple formula and if you look here what we're going to do is the starting day is a B for right the starting day is a B for so what I've done here is we're say we need to find this starting day in our list of weekdays now we have our list of weekdays here week days we've set them right here right here's a list of weakness okay so what we want to do is we want to find Sunday Sunday's the first Monday is the second so ill say we let's say we want the first day to be Monday let's go ahead and set that to Monday and we've refreshed the schedule so now the first day shows Monday so what we want to do is we want to first we want to match this so we know that Monday is number two right we know that Monday is the second day so when we match a be four which is Monday against the weekdays our result here our result for this formula is two so we want to go minus one two minus one the reason we want the one is I want to click on before so this is our starting day of the week our starting day of the week is always going to be Sunday right we want this to show Sunday the reason is is because we have to start at some point so for example when we click now here's our Sundays in April so if we click 23 our starting date is gonna be this is always going to be the Sunday so if we click another 116 our starting day is going to change the 15th and here's the reason basically what its gonna say it's gonna take this start day whatever day we select and it's gonna find the Sunday this formula finds the Sunday it's the weekday of B 3 minus B 3 minus the weekday plus 1 so this all this formula does is to find us the Sunday so this is gonna show whatever Sunday of the week so now all we need to do is say okay if this is Sunday and we know that the user wants to start on Monday it's gonna be this plus 1 if the user wants to start on a Tuesday it's going to be that Sunday plus 2 right so that's Sunday so if we're looking we're gonna match they want to start on Tuesday so a B 4 equals Tuesday this result this result right is going to be right Tuesday is our third day Tuesday is our third day here one two three so we we know it's Tuesday and we're starting on Sunday so we need to add to my name to Z so we need to add to to get to Tuesday so we do that here all we need us to do is be four right b4 it's just here plus how many days plus two days so it's gonna be the third day the finding Tuesday is a third day in that list minus one is two so B for Sunday plus two so that's how we get Tuesday right you can study that for me a little bit more it's not so complex and then for the Romania's all we do is just add one add one and add one to get there in many days of the week so that is how let's go ahead and go back to Sunday and we'll select on this week so that we showed we have the data we're done and when we click we'll go ahead and go over the macros we've got four different macros that run each of these buttons we've also got a refresh schedule so let's go ahead and go into it into the VBA module we go from your developers tab you can click on the visual basic you can also click alt f11 if you don't have the visual developers tab available you can go in to options here and you can select the customize ribbon here and make sure the developers tab is selected here alright let's go ahead and first we're gonna go over the on sheet macros and none of the other sheets contain any macro is just the schedule the other sheets get deleted or added and they don't contain any macros on the sheet itself we do however have a few different macros that we run we have a few that we do run on selection change and we have a few that run on the worksheet change now the idea is now here's the idea let's go over some some of the methodology now the idea is it gets a little bit tricky let's say I want to change this to 15 minutes right I want to change this to 15 minutes I need to make sure that first we clear out any and all of the colors that focused on that so if we're if we are at an hour 15 minutes we've got five rows that are colored and if we change that to 15 minutes we need to make sure that we've cleared out all of the other cells right so how do we do that well the first thing is if we're gonna make a change I need to know what it was before so for example if we click on our I need to know that four rows are colored currently color they need to know that how do I know that well what I want to do is I want to save this as soon as I select I want to save that information one hour I want to know because if I make a change I need to know what the old value was if I make a change to 15 minutes I need to know hey there were four cells that were colored before it you need to clear those four out and color just one so we need to know so what we want to do is we want to save this as soon as we click and I want to save that and I've done that with this line of code right here if you'll notice this line of code selected duration this is 15 minutes and it's not necessary import' you don't need to know the decimal but so if we select on 2:45 this number is going to change and the idea is is we've used so we need to know remember we need to know how many rows I need to know how many rows are colored as soon as we select on something to do that we just use a simple formula so on selection change within the code what I'm saying is if there's on selection change all we need to do if the user selects any one of these be six te 101 h6 2h 101 k6 to case k 101 that is these columns right here ecigs right here all right h6 right here k6 so these are all the duration columns and in queue so basically if the user selects any one of those from row six to 101 anyway then do something and you want to make sure that the target value if it's empty you don't really need to do anything so we want to do those conditions what if it has the user made a selection on row those two is the current value something does it have to contain a value if it does if both those conditions are met then do something what are we going to do all we're gonna do is take the target value and put it into b5 that's all we're doing is we're taking the value and putting in a w5 so that's all we're doing here in fact we could format this so it's a little bit more clear it's not important but so let's go ahead and let's go ahead and format that is the time so you can actually see it it won't change the value it'll just make it clear to you what we selected so now when you see it now let's take a look as I select it it changes okay so but I but I don't need to know the times I need to know the number of rows that we need to clear it if the user makes a change I need to know that six rows must be cleared out I need to know that it's important so we know so to do that to get those six rows I need to know it so we've used a formula here and all we've done is we're matching we're using the match we're saying basically where what what 4545 appears one two three third in the list so I need to know three so all we've used as a match match b5 in the range duration and we need an exact match if there's a match tell us what row tell us what number is found so 45 would be 3 15 minutes would be one so when we select 1 15 minutes would be 1 and so on and so forth so hour and 15 would be 5 so here we have that so now we have this value now we know the number of so should the user make a change now we know okay we need to clear out 5 rows so now we know so if there's a change we know the number of rows is right here in b6 and let's go into that so I can show you when the user makes a change the be using worksheet change right here we use the if target count greater than 1 this helps us avoid bugs so that when large portions of cells are selected no bug appears if we don't have that but probably will appear when we make a change selection change or something that's why we have it both on selection change and work sheet change so that's helpful in voiding bugs however if you're using a merged cell keep in mind that some things won't happen right so if you want to make a change and you want the code to do some action on a merge cell and you have this here you may need to change this to two or three just in case so let's go ahead and go down and here we have if not intersection D 6 through 101 so basically this is the entire schedule e 6 through 1 R 1 is our entire schedule so let's go ahead and take a look at that d 6 e 6 is here the first cell that user can make a change and to the last cell which would be X 101 that is the last cell that user commit change so here I want to know if the user makes a change to any one of those do something so if they make a change and something else does and we need another condition B 2 is false what is B 2 B 2 tells us when the schedule is loading right when the schedule loads all the data here will be put in but I need to differentiate between when a users putting in something when a user makes an action right and when or when a disk a jewel being professed so we click this week all the information is cleared and we put in again that's also a change so we need to differentiate between those two types of changes those are both changes one is automated by the Excel refreshing the other is in user input so we need to differentiate those two so if you'll see here in b2 blowed week right this is the load so when so I've done I put a condition and when we refresh this week this goes to true if your mom see it's gonna be really quick you're not gonna see it it's way too quick but basically this goes to true and then it goes back to false again so this allows us to differentiate between a user input and an automated Excel refresh so what we do is we want to make sure hey this this needs to be a user input not a refresh so you'll see in our schedule refresh Mac will go over this a minute we automatically set B to true so we know that the schedule is going to be refreshing automatically and we need to differentiate that so in this macro we put it to true back on the scheduling macros so we need to we need to get a few things we need to get the sheet name of where we're going to be saving that we need to get the sheet name and what I do is now keep in mind that for example let's go ahead to the beginning of the year and so I can show you how it can differentiate on one single a week if we click a January 1st of the beginning of year you'll see that Sunday December 31st is in a different year so when we are going through these and adding information to the database we very likely could have a different year in one day and then the next day so we need to theoretically in if we make a change to this day we're going to be saving in the 2017 database whereas we make a change on this day we're going to be saving in the 2018 database so that's really important to know and so we need to be able to make sure we know which database we're going to and I've done that through some notes all the way down here into row number 110 in row number 110 what I do is I'll pull the year from this particular date on both of these fields I'll pull the year and all I've done is using the year of d4 and I've done that the same for each so each one is the year based on the date above so this tells us exactly what database or sheet to save in this particular formula tells us what column to save that in and the column is simply a using the match indirect and match and I'll go over that so let's go ahead into January 1st and January 1st we know it's column 1 the first day but let's say January 2nd I want to save this in I want to get that third column so when we go into 2018 we want to save the information when we save it under the second I want to know that's column number 3 column number 1 2 3 so want to know start saving this information under column number three so we need to so what we're gonna do is we're gonna use the match and we're gonna find the date all we need to do is find the date in this first row here find the date so our formulas gonna say find me the date in this top row and tell me what column that date is so this is what our formula does here if we look at this formula we're gonna use the indirect to get the range I need to know the range because I don't need to know so what we do is we're going to say first what is the sheet and then the range now the range is always going to be a 1 through abd 1 the so the regardless of the sheet the range the range is going to be the same however the sheet number will change based on the year so we do that in this so we do that through the indirect so when we click on here we'll see indirect k 10 and we surrounded this by quotations the reason is K 110 that's the year 2018 if we don't add double quotations here and there Excel indirect has problems because it's just a number if your sheet if your sheet name is text it won't have this problem but with I've noticed with sheet names that are only numbers you could technically so this enforces Excel to see it as a text so that's really important and then we said here's the range a 1 through a B 1 so all we're saying is this is the range we need to find this range and inside that range I want to run a match match is J 4 I want to find this date in that range and I want you to tell me what column you know or what column that's in so it just simply returns the column so January 3rd is column 5/4 its column 7 so all that does is help us and this all this is the the column number so I've just added this is because we're gonna run a loop a little bit later so we want to run through all the columns so that's just help it's not necessary it's here to show you that we need to run from call number five to number 23 when we're filling in the data so that is how we add to the database so basically when we make a change let me make a change it's gonna say take that 30 minutes right take this information put it in column in this case in this case he'll it'll be put it in column three right and then put this in column 4 so that goes in column three and that so when we click on 18 and we look at column three which is here and we go down and we will see that we've added it oh here it is and where let's go ahead and pull that up January 1st 2018 okay so that's the first sorry so the first is right here that's call them what column 30 will be here so when we make a change to column 3 this is column 3 this come for this is calm 1 & 2 so Peter okay so here's your column 3 & 4 going back into the schedule you see under column 3 column 4 is Peter so that's all we're doing is we're taking this information we're storing it in the database based on the column number and the row number we're gonna use the same row number that makes it easy so row number 31 here and also row number 31 here so the road number is not going to change that makes it a lot easier I just used the existing row number whatever row we're using here we're gonna use there so it's pretty simple as far as well so we do that within the code as well let's go back into the VBA code where we can see more about that and into the scheduling screen we go so what I need is I need to get the sheet name and the sheet name is row 110 which you just saw and the target column and then the call and remember the column is below what column are we gonna put that information in the database column is in row number 11 and the target column okay so row 110 and row 111 right here row 110 is where our sheet name our database name is and row 111 is the column that we're gonna be storing him each column has a different number you see this is the last column in the sheet because it's the last year 729 this is the first so that's how we get the information in there so we need to know both the sheet and the column number that's very important so we defined those and now we say now we need to differentiate between if we're making a duration change or if we're making a change on the details so I need to differentiate that because we're gonna be doing different different things if it's a duration change we need to clear any old colors and add new colors if it's a details change it's just a simple save to the database so again to a few things going on here if it's a duration change if it's a change in this column here we need to do we need to clear the old colors and add in the new colors right clear the old if it's a change to this column then it's just simply saved to the database there's nothing to do there so we need to differentiate those now how can we differentiate those well each one of these has a different heading in row five this one's called duration and in this column is called details so we can use that differentiation in the text titles there in the headers to differentiate between those two columns so back into the VBA code let's go ahead and take a look at how we've done that so all I'm saying here in this if sense even if cells Row 5 remember that's the header row and the target column equal duration equal D you are and and the cells target column value right that equals does not equal empty and this means if there is there has to be a column we want to make sure that there's a column number in Row 1 11 because if it's empty then we we can't add it to anything so as long as those two things apply then do the following first we want to update the colors but before updating the color we need to clear the old colors out so we need to make a check if b5 remember b5 holds the number b5 holds to right when we select here b5 holes three here cuz right so if long as we're saying excuse me be b6 if b5 b5 is hold the the time and b6 holds the numbers so they're both very related so either one of them if they're either one of them are blank then we don't want to do it as long as there's a value then we know how many colors to race so we're saying if b5 does not equal NP you change that to b6 both will work though both will work I want to make sure if there's an air I don't want to continue so I've added if air if air in b5 you know then go ahead and make sure the spikes so let's go we'll keep it at b6 because this way if there's an error we will not continue as long as it's does not equal van right then what then we want to let's go ahead and get out of that cell okay so I can highlight now and okay so if b6 does not equal empty then what do we do then the range of the cell so now we're now we're now we range this means more than one cell right more than one cell and this is cell one this is the beginning of the range and this is the this is the upper left cell in the range and this is the lower right cell in the range okay so that is how we do that so what I want to do is here's one won't say let's say we've got an hour fifteen minutes and we're making a change I want to know the upper left and the lower right so I need to clear these colors out and I know I know that it's I know because of our formula I know that it's gonna be five rows so I want to go from this cell to this cell and I basically want to clear them out or make them white in this case we'll make them like so I want to take all these cells and make them white before I recolor them so for example if I delete that I want to make sure it's cleared all the way up and if I had 15 minutes or add more I want to make sure they're colored so we do that through this line of code right here and what we're saying is the target row so we know excuse me let's start at the first the target row in the target column that means we're starting at the first the / left corner whatever the row we've changed and whatever the column we changed that's the first now we want to make a change now all the way I want to go all the way until the target row + b6 value minus one so remember b6 is the number of columns so let's say we have five rows that we need to clear out or we need to change the code away if we have five rows then we go b6 is 5 minus 1 is 4 so we're gonna say the target row plus 4 target row plus 4 will give us the number of rows that we need to clear out so that is that is the row and what about the column will we want to go the target column which is the first column plus 1 target column plus 1 here's our target column right here plus 1 so we want to go 5 rows down and 1 column over so that's how we get our full range so now we know our range now we know arrange now what do we want to do we want to take that range and I want to change it this is white 1 6 7 7 to 1 that color is white that will change or our color or interior color of those cells to white so that is how we do it that's how we get the right now what we need to do is we need to recolor recolor them and so also we want to do is we want to set b5 to our new value what is our new value right so I want to make sure that we know how many so our let's say if we go to an hour 50 minutes and we change it to 1 hour our new value in b5 is 101 so I want to make b5 our new value and then I also want to get our new number of rows our new number of rows is four before it was five now it's 4 so I need dot b6 I need to know how many rows to color now so we have that here so b5 equals the target value this automatically calculates the number of rows in b6 so now we're going to make sure if the target value is not empty make sure that I actually put in a time then we need to get our new range our new range is the same as our old range because we've changed v6 is now updated with the new number of rows so it's the same the only difference is we're going to change that color to whatever fill color is here we need to get the fill color so our named range I've named it rains this column is called fill color I've named that range so we can use that so that means whatever color is in here I need to get that color and I need to change the color of it to anything in here so that's the color we're going to use so that just one line of code does it so it says find the fill color here and use that the interior color of your new range must equal the interior color of whatever this fill color ranges so when we change it it's changes easily so when we when we change this color and we can do that simply by let's change it to yellow and now when we now when we add a new one automatically goes to yellow now all now once we click refresh this guy so all of them will change but so if we go previous week and next week to all change to yellow and that because it uses this same color each and every time so it's really really convenient let's go back to the blue we were at something a little bit easier on the eyes and once again next week and back and boom it all changes back to blue so when we run our scheduled refresh it also takes the color the same fill color interior color so it pulls that the last thing we want to do is basically add that time into the database but in actuality we don't want to add the time right I don't want to add the time I just want to add the number of rows that are going to be affected or in other words then where that time fills on flies on that list so for would be one hour one hour on our list is right here right here's the fourth one down it starts at 23 24 25 so one is the fourth Bish's and I want for I don't want the one hour so we do that with the last line of code here the sheets the database sheet we know the sheet right and again we've surrounded that sheet and double quote that helps us when we use the sheet names that are only numbers so that helps us push Excel to know that this is a text and then we have the target row we know the database column we've defined that up here we know what column we're going to be using and so we know the sheet we know we're gonna use remember the same row in the database is the same row that makes it easier we don't need to change the row equals b6 value remember b6 is the converted it's automatically we don't need the target value because b6 is automatically the number of rows 30 minutes is two rows right 45 minutes is three rows so we know b6 holds that number that's what we want to put in our database that's the number so that is how we take care of it now the next thing is details if cell five of the target column remember our header row row v equals details then we know they've made a change and another thing we will also want to make sure again that we do have a column we want to make sure that we have a column this prevents heirs in case there it cannot find that column in the database I'm gonna make sure that that's not empty and then what do we do here all we're doing is saving it in the target row and remember the database column is the first column of the day so we're going to be going into the next column plus one and what that means is we want to in our database here's our column right our column is let's say column three when we want to save the details we want to save it in one column over just the next column so database call them plus one will get it here database column is here so that is how we get our target value in this case target value the name we put in will show up in our database and that's important because we need to recall that we need to bring that back when we refresh it now the last thing is if there's a change to a few things I want to refresh the schedule this is a macro scheduled refresh we're gonna go over that next but basically what I'm saying is if there's a change to be three or a before please refresh schedule what didn't be three-and-a before well b3 b3 is our starting date for the schedule B 3 is here so if we make a change to that now every time we go previous week and every time we go next week that there's a change to that cell so anytime there's a change of that cell we also on this week it also changes and also when we select a specific date it also changes so so anytime there's a change refresh to schedule so also if there's a change to a b3 I also want to refresh the schedule if we change this to Monday then also refresh the schedule because I've got to refresh it and if there's a change again let's go back to Sunday refresh the schedule so those two items I want the schedule refreshed let's go back to this week now we've gone over the on sheet macros so we've covered that we've covered this now let's get into just the other modules here and code reset is something you've seen before this helps us move the application faster all this does is disable events with stop code and re-enable events calculations and screen updating and this helps a screen updating I'm going to comment that out it may not be so may not be so important sometimes can cause issues so that's not so important but the other two are are much more important and that helps us run the refresh much faster so all of our macros reside on this module here and we have I think four or five scheduled refresh is the biggest one we have open calendar and what this is going to do is its selects b3 where the dates going to change and open our calendar we'll go over that in a little bit detail we have previous week we don't need that and we have this week and we have next week so we have those three very small macros that basically just run and I'll go over those but let's go over the big one first and let's go first one clear that out there we go I'll just get rid of these here we don't need to refresh because the reason we don't need to risk is because any time we make a change to b3 it automatically refreshed so we've actually saved some lines of code there that means the schedules automatically refresh every time we make a change to this so that's why we don't need to run the macro schedule request so or saving lines of code right and fair so we've got those of four Macker at the bottom let's go into schedule refresh and let's go over the methodology first and then we'll go over into the detail now the idea of this particular macro is basically I want to one I want to run through every single column I want to run from column number five to column number 23 and I want but I don't want to go through every single column I want to go five to call them eight to call them 11 to column 14 so I'm gonna skip right so but because I don't need to go through every single column I can do this plus one so we're gonna go we're going to go 5 to 23 and we're gonna step 3 then when we're on each column I want to run through every single row here and add data I want to add data from here from the schedule into the schedule but what I don't want to do is I don't want to run through all the way to 101 what if there's no data down here it's it doesn't make sense for us to go all the way down if there's no data let's just go to the last row of data and then once it's the last row then let's go to the next before we pull it in so we've done that we only want to go to the last row of data we don't want to go all the way to row 101 when there's no data in here it's it's kind of a waste of time so we're not going to go to all the way down to 101 really you use the last row of data and we're going to go through every row every single groan we're going to do that and we can do it quite quickly especially when we turn off calculations and turn off screen updating so that is the methodology I want to pull all the data in there and then when I do that I want to make sure that we update the colors as well so we're gonna do that that's the schedule refresh the first thing we want to do is we want to our schedule refresh - true and what this is gonna do is gonna stop anything it's going to make sure that when we that this stuff doesn't run when changes are made we want to make we only run those when b2 is false right we only run these when b2 is false so that means things in here won't run while we're up getting it we don't want to these things in here only run when we manually make those updates that is the reason why we set that condition that was the reason why we set it to true first thing we want to do is we want to clear the colors and data of the existing schedule we want to make sure that we clear out all this data right we want to just want to delete that we want to clear the data and we want to clear the colors so we want to do those two things clear the data and clear the colors so we can do that with just also two lines of code we know the range is here we're gonna say with this range and these this range is the same range of this we're not of course we're excluding the road columns with times we're just including both the duration and the detail so I've covered that in that particular range here we're gonna clear any of the contents remember we're going to clear the contents we don't wanna clear formatting we don't want to clear anything and then we're gonna change the color to white we're gonna change the color to white on those so just those two so that clears out and then we'll really just want to calculate it I want to make 100% sure that these form that this formula gets calculated these are very important right so I want to make sure that these formulas get calculated so we're gonna run the calculation so that's an important part of that when the dates change right we want to make sure that these formulas calculate so we can get the exact column numbers that's also important for this so now we're gonna run our loops and we have two loops as we mentioned we're gonna go from column to column to call them and then row to row to row so we have for week five 223 and you might remember those are the numbers at the bottom and then we're also going to run a for next loop for the rows here for scheduled row equals six to the last one we'll define what that is so again the columns 5 to 20 step three we went over that just a bit ago we're gonna go through all those columns in the week we're gonna step three so to do that we're gonna first start that loop and the next is we need to pull the sheet what is the sheet its bro 110 and the week column so row 110 who's gonna pull up because different days can have different sheets so gonna pull it up pull that sheet up row 110 and whatever column runs so we're gonna get that sheet name each time it could be different if we're splitting a year in a week so we get that and we store that here in this variable under database sheet which is a string a variable which we've defined up here I don't think so we have done that and now we're gonna say now one thing is if what if it's empty what if 111 is empty that means that means there's an error what if this is empty if err empty the only reason we're gonna have an air is maybe this she doesn't exist right maybe this sheet doesn't exist so if we change the dates to let's say 2020 I well actually it's gonna it's gonna it's gonna create the sheet automatically right now no matter what because let's close that out let's if I'll just change it here it's much easier if we change this date if this is 20 let's say to 0 to 5 right these are gonna become blank you see because the this formula is looking for this it's looking for this date and it doesn't have there's no Year 25 there's no sheet 25 so it's gonna be blank that is why we run a test if this is blank that means we must create a new sheet that contains those dates so that's the test we run first so here it says if 111 equals empty then we need to create a new sheet we can do that with just these lines of codes here so it says if the calculated column is empty that means the in the air does not exist so we must create it we do that here we can use sheets add this is gonna add a new sheet in the workbook and then say well what it what name do we want to call this that's the database sheet we have that we know the year so we're gonna create a sheet name with that the active sheet of our new sheet is going to have the name of the database which is going to be in that case it was gonna be 2025 and then what we're gonna say is okay with the sheet 2018 let's go ahead and copy this with that sheet copy so that means here with this what I want to do is I want to go up here and I want to basically just copy all of the dates now we're gonna change the dates but I want to take this entire roof all the way from a1 all the way over to ABD and i want to copy everything i want to copy that and i want to put that in the new sheet so we do that through codes so we're gonna copy a 1 through ABD then in the new sheet which is here paste everything paste it all okay but even though now it's still got 2018 we still need to update the dates because it's still got the 2018 and we'll do that with the next line of code so we're saying that new sheet range a1 should equal the first day should equal January 1st of and then we're gonna add the year right in here so that updates all that to the year because everything is based on that formula so for example if we'd go into 2023 and all we do is make one change right change the year to 2022 all the dates in there change so that is helpful for us all we need to do is change that one date and they all change so that is what we do here change that first date everything else changes now we have a full database with accurate dates for that specific year next line of code is we're just going to exit out of the cut the cut copy mode and also if you want to hide the sheets you can use this line of code this will hide the sheets but I've left to calm down we want to see the sheets for training purposes I want you to see those sheets but you very well may want to hide those in case you do just uncomment that and then it'll automatically hide that sheet so that'll be helpful I want to go I want to send user back to the scheduling screen and case is not active so sheet one it'll activate it and then we're gonna calculate I want to make sure that we're calculating that that new sheet knows formulas all calculate properly so we can get those columns so we've gone ahead and calculated here alright so now we've got the new sheet now we can continue on because we have the sheet we have the Davis and now we can store the details that we've gone so we can now set the database column to the week com2 row 111 and the column we can set that database column so we know what column in our database to store and also now what I want to do is I want to know the last row I want to go in here and I want to know the last row for example into the 2018 database if our last let's see I think in this case our last row is seven right our last row 13 excuse me our last rows 13 on the fifth right if we look in to the fifth here here our last row is C it's the wrong week let's go ahead and pull that up January January let's go ahead and pull up January and we can see what our last row is so you can get some idea of how working in that now January first our last row so we go to our last rows but in this case 31 January 2nd 2018 our last row is 31 so I don't want to go beyond that I don't want to go beyond that so that's going to help us out that we don't need to go through every single row just gonna go through the last row in this case in this case it's 31 so that's how we do we need to get that last one we could do it in the code right here the last row equals the sheets of our database right the database the 102 is the most possible last row we can get 101 is actually the the highest possible row so we'll go one above that and we're gonna say database column one that would mean the details the details column what is the last detail so we want to get that last row so once we have that last row we can say okay now for from six to the last row but before we do that what if there's no data what if that last row is less than six then we can just skip the column entirely there's no there's no need to go through every cell in the comp there's no data so if that last row is less than six in this case if that last row is less than six we don't need to go for example this row has no if there's no data let's just skip it entirely so that's what we do that helps us really speed up the code so we do that with this line of code now we're gonna run our loop from six to the last row and we say the cells the current cells the scheduled row now we're going through this scheduled the scheduled row plus the week com plus one equals the database sheet the scheduled row the rows always the same and the database column plus one basically what this does is it takes information here right on January first fret and it puts it right and row six and it puts it right on our schedule right here it puts it right here so that is how we do that and in this case January first so it'll put that right there let's go to this week where we have some data that is updated all right now we have all this data and so we can see that there's a good amount of data on this sheet so we're gonna basically run through adding the data from the database into that cell now we want to adds we've added our deployment details with this line of code now we want to do two things I want to add in the duration and I want to color it and we can do that with this line of code first we want to make sure the duration is not empty so we'll use this line of code the scheduler Road and the database column is not empty if it's not empty we're set the duration to the database column the duration that's a long file that's a whole number and our duration is gonna be that whole numbers gonna tell us how many rows to color the duration in this case is four is six for one this tells us also how many rows to cook so hour duration we've set it up and put it in this value here now I want to take that duration I want to convert it to a time and I want put it in there so I want to take that for I want to take this for under David Davis I want to change it to one hour and I want to put it right on January first let's go to the current let's go to the current week we have which is currently April 29th right here all right here's our information so for example I want to take Lisa Smith I want to take this three and I want to convert it to 45 minutes I want to take this and I want to convert it here so how do we do that well we know one two three is 45 minutes so a 25 is 45 right a 25 is 45 we know we have a so a 22 plus the duration is this number a 22 plus 1 plus 2 plus 3 is 45 so that's all we need to do we know it's in column a so that's exactly what we do in the code here so cells 45 the week on a equals a and 22 plus the duration if the durations 3 we're gonna be on a 25 that's it that's all we have to do is to convert the 3 to 45 so that sets the actual duration based on the list of durations so now we have it now all we need to do is we need to color those we need to color those three rows we need to tell Excel okay I want you to color those three rows I want you call it I want you to start here and I want you to end right here and color this and I the color I want you to use is right here under fill color so that's it we can do that with just one line of code also arranged because we're starting with one cell and we're ending with the different cells so it's going to be a range our starting cell is the schedule Rho plus the week column that's the upper left and our ending rows the lower right cells the schedule the row plus the duration minus one this will get us this is three minus one is two so this is two two plus the schedule is a total of three rows and we're gonna add one the column plus one so this tells us the range to color this is the range and now this tells us what color is the color of whatever color is here in the fill we went over this before that's the color we're gonna give it so that is how we color it and then we just loop through them we go to every single row now if there's no data in the row that's gonna skip go to next column and that's gonna skip all those rows so then we just go to the next column and then we loop through that for every call now when the schedule refreshes what I want to do is I want to I want to also as we said I want to go right here at our first I want I don't want it to end up here I don't want to end up here I want to end up right at 6:00 a.m. 6:00 a.m. is our first starting room so if we change that to 7:00 a.m. or two let's say I change it to 5:00 a.m. I want it to show I wanted to show five M so I want to automatically scroll on the refresh to 5:00 a.m. based on that because I want to go to that first time so that is how we do that and now it's 5:00 a.m. here so let's go ahead I'm gonna see if it runs a little bit quicker let's go ahead and go into the code it's a little bit slow on that full week so let's go and I'm going to enable I'm gonna also stop screen screen updating let's see if it's a little bit faster there so we can we may need that see if that helps at all and click on this week again oh yeah there we go much faster okay so we'll keep that up so screen updating in this case does help us a lot so we're gonna keep that that's important you can see the time difference is there so the idea is I want to scroll to whatever to the start time is so how do we do that well this start time is 5 a.m. in this case and that's bro 26 so we need to know ro 26 that's important how do we find ro 26 well we know the start time is is 5 o'clock so let's go ahead and get that we know that all we have to do is find now we've I've I've actually created a named range from here to the end and we've called that x that is our range okay just the first column column D is x so if we find 5 a.m. let's say we're looking for 5 a.m. and we use a match right then we can use the match and we know that start time would be 5 a.m. right we know that it's 26 so we've done just that with this formula here so we're gonna match start time with the x right that's this column here and we're gonna add 5 on to that why do we add 5 because because our range starts at Row 5 because our our it starts on row 6 so we must add 5 to free if for example we didn't have that 5 it's going to return Row 1 I don't want I don't want more one I want row 26 I want to if so that start time is 12 I wanted to return 6 so that's why we add 5 on to this so this tells us exactly what row we'll need to scroll to when we refresh it so when we click this week automatically it Scrolls to row 26 and we did that with just one line of code let's go back so that is located in b7 b7 so that is what we want so our our code will tell us to do just that it'll say active windows scroll scroll to whatever is in value b7 so that's gonna scroll to the row b7 so that's how we do that the last line of code all we do is refresh that schedule to false so that then remember that why that's important and then we're gonna reset the code this reset code is also here this is what stops our calculation stop code and resets the code enabling events disabling calculations that's we keep things working really fast if we don't use that it's gonna run really really slow so we want to make sure that when we're running through all those columns and running through all those rows that events are disabled debt calculations are manual and the screen updating is false when we finish it we do absolutely have to make sure we reset the code otherwise things are not going to work so we want to make sure we do reset events calculations and updating on that so that is why now the open calendar all we do is we're going to select b3 and the reason we selected is because when we click on that calendar form here right click on that calendar form it's gonna our code is gonna basically tell us to put the date in the active cell I'm gonna put the data in the active cell active cell value so we want to make sure that before we launch that calendar we click here okay we click here excuse me click here b3 right so if we're clicking here and we click the calendar you see automatically b3 got selected that way when we put in a specific date automatically it gets updated so the that's why we select that before that is why the code we select b3 that is our date that's important and then we're going to show the calendar that's it that's all we do for previous week all we do is take that same day and b3 and we subtract 7 from it for the current week all we do is we take the current day which is located in b1 our current day is located in b1 here and we just add that we make that so when we click this week it takes the current date and puts it right into the week start date so that is how we do that and as well the next week all we do is take b3 whatever is currently in there and we add 7 changing b3 automatically updates the schedule automatic so all we need to do is make a change for example if we go into b3 double click and exit out it's gonna refresh the schedule just like that so theoretically we could also change this and that itself would update the schedule as well so that's how we do that that is how we update the schedule there those are the macros that run this particular calendar so it's a it's a really really important I think we have gone over everything in here I hope you do like this training video a little bit longer than normal and I hope you found it interesting I would love to see how you customize this and make this use in your own applications your own as always these videos are free I love to make them for you and love for your feedback and comments all I ask is that you do share this video subscribe to our YouTube channel if you can you'll get automatic announcements if you click that icon thank you again so much and have a great day [Music]

Channel: Excel For Freelancers

Views: 40,882

Keywords: Excel Scheduler, Excel Week View Schedule, Excel Schedule Week, Scheduling in Excel, Week Scheduler Excel, Excel Week View Scheduling, Excel Appt Scheduler, Excel Appt. Scheduler, Excel Appointment Scheduler, Week Appointments in Excel

Id: i8OHhg1ZKqc

Length: 73min 50sec (4430 seconds)

Published: Tue May 01 2018