How To Create A Dynamic Appointment Scheduler In Excel [Part 1]

[Music] hello this is Randy with Excel for freelancers and today a very cool training in which we're going to be creating an excel appointment schedule that is completely dynamic that can accept multiple appointments for a single day and any number of months and years it's completely dynamic so I'm very excited to show that to you today and how we went about it with the formulas formats and techniques as well as the coding of how this is all done so stay with us it's going to be an epic training all right thank you so much for joining me today this Excel scheduler is incredibly dynamic in which we can create a fully customized appointment scheduling we can schedule any appointment simply by typing in the name of the appointment and then that's automatically saved to the schedule we can go to the next month and then go back and have that information appear as well and as well we have the ability to give the user a different start days we can start this on Sunday start the week on Monday Tuesday or Saturday we can change the start time as well we can also change the interval too so if we were going to go ahead and change to 15-minute intervals or we can change it to one-hour intervals and we can also change the scheduled year as well okay so let's go ahead and get started and show you how this is done all right in this particular training we're going to spend a lot of time talking about named ranges and those named ranges such as months right or weekdays okay and those are used throughout our formulas and they're really really important here because they really help us with our formulas and they help us to code faster as well so we're going to show you how those name ranges are used both in formulas and in VBA cody's so that we can rapidly create accurate code that helps us get our products to market and to the customer as quick as possible okay so let's get started so first we have to define multiple types of named ranges and if you look in the formulas and named ranges here you're gonna see several in fact let's say there's so 10 or so named ranges maybe 11 of what we created so let's go ahead and go over those so we can start to define of how we make the foundation of this application okay and first of all I've created a list of months this is a very simple months and to do that I've created that and remember in in Excel we can do that simply by just clicking and dragging down okay so it's very simple just like that only do is start out with the beginning and then I've gone ahead and created and then what I've highlighted this right all I did was enter the months here so you could just type it in months right if your name is now ranged and that'll that'll define it I've done the same thing with days as well right start off with just a single day typing it in right double-clicking oh that's too far okay let's go down to back to Saturday okay and that's it and highlight that and I've already defined it for you but you could also highlight all and then type in weekdays okay so we've gone over months and weekdays also if you want to change the name it's not gonna change here okay I don't believe so it's not gonna if you do want to change the name she'll need to go into formula named ranges and then go into here and then click Edit and then you could edit the name here okay if you want to change the name so you have to do it from the name bridge okay and so we've got months we've got days we've also have times and now what I did is I've created a times and I want to give the user the ability to add in a start time right and I want to include basically all times at 15-minute intervals okay so define this list I've created a time list okay and I've started at 12:00 a.m. and then the next one is at 12:15 and 12:30 so the quickly a way to quickly create that is just simply by let's go ahead that simply by highlighting this and then extending this down as well so that that's world and the format let's go ahead and take a look at the format that we use cuz in excel the informant is not in here we'll have to set that as a time format clicking here time and that's going to include the seconds I don't want to include the second so I'll go ahead and click on more number formats and then I'll go ahead and find the time that I want without the second so we're gonna use this if you're on military time you'll probably want to select this okay so if you if we're just like this you'll see that changes it to military time but we don't want that so okay so we've got time to defined and if you'll control shift down arrow it'll highlight all the time can you see times has been set okay so that's helpful for defining our time so we've got time and now intervals let's go ahead and take a look at how did I get I want intervals right because I want to be able to do user to set how often this schedule and I want them to change be able to change that within a specific range I want to have that in a dynamic list here okay so fifteen thirty and one those are the time that I want now how did I create that well we know bright that a day is one right in Excel one is a day okay so that means that one hour is one day divided by 24 okay if one day divided by 24 that's the number of hours and it's basically a decimal format right and I've reformatted it so if we take off that format right and we go back to general we'll see it's really a decimal right but in excel we have to change those to a time format okay so remember one is a full day so anything less than a full day is going to be a decimal number okay less than zero okay so it's important that we know that so to get to one and now of course to get to 30 you can do it in several different ways it could be one divided by 48 or we could simply divide be 25 divided by two which is this one here divided by two and then 15 minutes is this one one hour divided by four so I've chosen to show you that and then I've named those ranges when we highlight those we'll call those intervals okay so those are intervals and then next up we have the Oakland Center that so I'll make it clear for you and let's go ahead and Center this one so that's also clear and back down to just one row okay and I wonder if I want to make sure those are very clear so that how we've highlighted this well I'll go ahead and darken those a little bit so we can see those column headers a little bit differently and that's clear okay and then the same thing with you I've just listed a bunch of years and then I've gone ahead and named that range years and now to create that drop-down list all we need to do is go into the data fields here data go into data validation and then click on data validation and then we just always select list right and then we select weekdays and this is remember this is the same thing if we were to if it's the same thing as doing this okay but automatically you see how weak this comes up because we've already named that range but if we were to change it and extend it it would be back right so you see it knows Excel knows that when we've selected the exact name range it's going to automatically put in so you can highlight that or you can also type in weekdays okay and then and then to check to make sure tap out tap back in okay tap back in and you'll concede the dancing ants around that that shows you that you've got the right data selected so that's a great way to verify your work and I've basically gone through the same process for the start times and through the intervals which we just discussed and the years so this enables us to quickly set up now how did we go ahead and create this calendar well why don't we go ahead and take a look okay now because our first day is dynamic remember our first day is dynamic weekday start here if we were to put Monday right Monday okay so p4i this is equal to P for so simply this is a link to this right here okay so now how do we create Tuesday that the reason is we can't just assume that it's going to be we can't assume that it's the next day right because if it's if this is going to be Saturday then we need to go back up so basically we've said I just created a simple form that says if d4 is Saturday then make this Sunday okay if it's not then what we're gonna do is we're gonna index weekdays so weekdays right you know is this you see the Green weekdays that's already highlighted match okay whatever want to do is match I want to match Monday I want to find Monday right where's Monday okay and then I want to drop it down one more so I want to find Monday where is it right what row is it on and then I'm gonna add one because I want to 'zed a right so that's all we have to do using index matches very powerful let me go over that once again we're gonna index weekdays that means we want to find something within this week is okay so with index we need to find two things we need to find one we need the row and two we need to call okay this this here is the row okay this here is a column now we're gonna stay on the same column so the columns easiest one but we have to find that row we have to find that row and the row is simply this locate Monday and then add one right locate Monday add one okay so that's all we have to do and we've gone ahead and used that index throughout it so I've just dragged and dropped that okay so that's all we have to do for that okay so it's very very powerful and so let's go ahead and set this back to Sunday that's what I'm used to and you'll see I've already set the calendar to refresh automatically okay so now we know how we get all the days of the week dynamically okay and so what we need to do then is let's go ahead and go over the formulas to get the calendar okay okay so basically now I we remember we discussed how we named these ranges but I've also named the individual field here this field is called start time okay so that means when I type in 9 o'clock equals start time okay I'd like that it's not necessary we could simply we can easily do equals this okay but we've named the range because it's nice to see that I like I like to see names in my formulas because they really help me define when formulas get complex right when you see those names inside it's really easy to understand what's going on so I just urge you to name your fields even if it's just one field or one cell sometimes it really if you know it's gonna be using a formula or in VBA let's go ahead and name that okay it's really helpful moving forward okay so back to this formula okay so what we've done is I've said if the weekday okay and now I want to know also one more thing we have to know what weekday this is okay that means basically what I want to say is if the first Sun if the first day of the month if the first day of the month lands on a Sunday then put that here if it's not leave a blank so that's what we want to find out if the first day of the month lands on a Sunday then put the day here if it's not leave it blank okay so that's the idea now the problem is that the complexity is this is not always Sunday right because this could be whatever they set here okay so the reason is what we have to do is I have to say if and basically what I've done is just put a formula down here since this is the first day of the week so I've used this formula a little bit of a helper side it's not it's not necessary we could we can actually put this directly in the formula but I wanted to separate them show you so what I've said is d4 d4 right what is this is the first day of the week okay remember when we use the match weekdays okay this is our first day there's a few different ways to do that but we know this is number one okay two three so we've gone ahead and run this formula and this helps us out so remember D 42 is the day of the week and this will change right if we change this if we change this to Wednesday you'll see the numbers down here change four five six seven and then it goes back one two three so you see these numbers change based on the day of the week and that's going to help us moving forward so let's go ahead and set that back to Sunday let's go ahead and increase this column a little bit by double clicking back to Sunday we go alright so let's go ahead and start out so basically what I'm gonna say here is we're saying if this date now the date is the scheduled year the selected month now the selected month is 1b for okay let's go ahead and take a look at that B for selected month we've put that month in here now when that month changes this this will change okay - if it's - if it's February is gonna be - March it's gonna be three etc okay so if the date the scheduled year the selected month and the first day of the week right if that equals if the weekday the weekday right the weekday of this date equals D 42 equals this number down here remember then put in the date then put in that date okay then the date is the first day of the year if it's not on a Sunday then leave it blank or not on that okay so I've done the same thing over except I've added something before if this is the first day of the week we don't need to test anymore we don't need to test this condition so if this is not blank then all we needs to do is add one so here if d5 is not blank then it's then we know then we know what the next day is it's going to be D five plus one okay if it's if it is blank then we also have to test is the first day of the month landing on a Monday so is the first day of the month equal to e 42 in this case right remember if it's two if it's two if in this case in January it is right so it is so that's in this case it does land on a first okay so that's a really really important fact that we want to make sure that we we use so so then all we've done is on this first row we've just run the same formula over and over again okay so it's the same formula and then it's easy after that point all it is is this date plus one the plus one this plus 1 okay so we just go through now towards the end of the calendar on the last two rows it gets a little bit more complex again because what I want to do is I want to find out is basically is what I'm saying is if this de plus 1 if the month of this day right now we know our selected month is as 1 right our selected month numbers 1 so what I want to do is I want to test a condition and I'm gonna say if this if this dates remember these are dates let me just show this to you again because I'm sorry it might be a little bit confusing let's go into home and then this is a custom format okay this is a custom format so in actuality if we if we show you their normal format it's gonna show up as date you see those are all dates right however I'm only showing the day ok however it's a custom format so if we go back into manage formats and I'm only showing the day okay I'm only showing the day D that's it okay and that's just gonna show the day so by doing that it only shows the day we don't need the month you could if you some people like to show a month you know you could you could on your calendar you can change that maybe you want to show a month maybe you want to show them like a three digit month you could go D - em D - mmm that would show that would look like this first of January you see some people prefer that you could do that as well if you like so that's why only numbers show but in actuality these are all dates okay just so you know they're they're all dates but they're shown but only the day is being shown okay so that's an important understanding as we move forward so back into this so we're saying is if this date plus 1 if the month of that is not equal right is not equal to the selected month then blink that means it's a new month for example this formula here there is no there is no 32nd of January right right there is none so we're saying is if if the month of G 29 + 1 G 29 + 1 right in this case it would be February 2nd plus one is for a second so that means it's not the selected month right G 29 the month of G 29 plus 1 is 2 the month of G 29 plus 1 is 2 it's February right so that's not equal to the selected month so in this case it's blank okay so in this case is blank and basically once we know this cells blank then we can test every cell after that all we need to say is if this previous cell is blank then make this one if the previous cell is blank then make this one blank so that we move forward throughout okay so that's how we do our calendar and we also have conditional formatting as you've seen I've gone ahead and colored the current day the current day Orange as you can see and that's done through conditional formatting and let's go ahead and take a look at the formatting that we have we've got on the headers on the headers we've got two different rules one gray if it's blank okay and this is really important because I don't want you to work too hard so on how to create these okay so for for the today was easy because there's no all I'm doing is testing for today so all I did was was highlight the whole the total table and then I just added a grool you know conditional formatting a new rule and then I just put format cells that contain right and then I just dates right and then today so that's all I did and then I formatted it so that was very simple because our I guess I guess if we put a date in here with probably 122 to 0 1 8 right it doesn't work because I have a pre I have another formatter right so I have the green format which takes precedence is on the selected cell and I'll show you how that works so basically all we've done but now for this I've done two different rules because I want to show for anything that doesn't have a date I want to gray it out okay so I've used two different rules one is on the headers and so all I did was highlight right holding the ctrl down highlight all of the affected rows okay now this is really important because I don't want you to have to create conditional formats for every single cells let's show you how to do them all in once control number I'm holding none in control now and I'm highlighting up I messed it up okay let's try that again holding down the control okay and we're gonna highlight every row that contains a date okay and now all we have to do is go into home conditional formatting new rule and we're going to use a we can use a formula okay and this is important we have to make sure that the first cell that is the one that we're gonna just defy and when we change this to d5 we want to make sure that there's no dollar signs we don't want an absolute cell because we want it to affect each one of them now keep mine something strange is gonna happen and I'll show that okay what are we gonna format let's just say let's just form in a green color because I want to show you something that's different I'm gonna show you how that works okay and then d5 equals blank okay another ste five is gonna change for some reason I don't understand exactly why it will change but okay now go back in there conditional formatting manager rules and make sure that everything said oh so it looks good okay so d5 so you see we don't need two rules but I just wanted to show you how that's going to work okay so d5 is Mike remember no absolutely no dollar signs cuz we wanted to affect only the individual cells so you see how that works okay and let's go ahead and back into here and let's go ahead and remove that rule we don't need that but I wanted to show you let's go ahead and delete that and so basically I now for this cell here for these I did the same thing right because I want I highlight using the control highlight every row and then in this case it's going to be d5 as well but just make sure that's not an absolute no dollar signs so that's how we create the in conditional formatting and now we did through some code I want to know what the highlighted cell is okay I created additional right if we click on a cell I want that cell to turn green okay how did we do that all right back into conditional formatting manage rules and you'll see this green here and I've done the same thing d5 equals M 3 M 3 okay let's go ahead and close that out right d5 because I'm three how did I do that M 3 is the date the current date right so basically it's saying if this date equals this date then color this and how did I do that same process highlighted all these cells okay just like this scroll down holding the ctrl holding the ctrl ok right ok conditional form any new rule okay use a formula to determine that yes okay select the first one d5 and you can use f4 f4 f4 f4 that'll remove it ok that'll alternate between just clicking f4 alternates until you find the one with d5 that's kind of a quick link using f4 format that alright let's make that red so we can see okay and and what do we want d 5 equals okay D 5 equals M what was it M 4 okay and M 3 M 3 M 3 will never change right because we that's always going to be that so that one cannot be can be absolute and does contain the dollar signs before the MN before the three so that's very important ok when we click OK okay and let me just fix that and so that is going and watch I want to show you something that happens Tennessee look at this you see that that happens a lot when the cell does not include it all we have to do that's very common because we just I don't know why but it happens all the time go back change that to 5 why does it change good question somebody must know somebody put in the comments why that changes it always changed back ok apply and it changes okay all we have to do is the reason it changes the reason to change here not here is because you see d5 was located in that d5 was located in that so it does change but you might want to change it back but it's not absolute it tends to change with the cells or it adds them up something some reason just make sure you know it's normal if for some reason it doesn't work go back and change that back to the way I was and everything will work out we can delete that rule for now so let's go ahead and delete that so we've shown you all the conditional formatting that works within this calendar here we do have one additional conditional formatting and that's simple and all I've done is I've if this I've added I can just format if there is any value in here use this faded green okay so that way I've got that conditional formatting and there's no VBA that these users can justify panic so it's really simple all right so let's get into the nuts of this and see exactly how this is working under the hood so we can go and see how did we create this calendar all right into the VBA model we go and you can click on the file if you don't have VBA options to get to that developers tab customize the ribbon and click here for the developers tab if you do not have it visible you can go in here and the developers tab click visual basic or alt f11 will also get you there let's go ahead and see what we have under here we've got a little bit of coding on the sheet itself and we have some macros that help us work not too many and I'm gonna walk you through those so don't worry too much all right so we have a few macros our first macro is when I click on a selected cell I want a few things to happen right I want that day to load I want all the schedules to load okay and now remember Excel only holds a value so the actuality is the data the scheduled appointments are not being held in here they're being held somewhere else as soon as we click on that and where are they being held well they're being held to here in this database right in here and I've and I and we have a new one for each year but I'm going to show you how we program that in automatically to create it now watch let's go ahead and create an appointment for 2019 all right change the date on that and then watch the magic happen now we have a database for 2019 just like that nothing else to do I can delete that now okay and I'll show you how that was done okay so now we can see how that's done go back to 2018 and I'll walk you through that how we did that as well all right EDA code and delete that now we can recreate another three seconds so no problem we don't need it right at the moment and so basically the idea is very simple okay and it can get a lot more complex all I did was create one column for each day okay and that column includes all the details and the idea is simple as soon as as soon as we schedule an appointment Fred on the 11th right that gets copied over here right here you see how it got copied over here let's do that again okay right under Fred David okay oh there it is right there David okay so the idea is to bring it here and and I'll show you a way to hide these you may not you probably don't need to see these so we can hide those automatically too but for our purposes I want to show you what's going on so I'm gonna keep them visible for you for now okay so the idea is as soon as we schedule we want that data to transfer to the right day here so that we can recall it now how do we do that all right let's go back into the VBA and show you how we do that all right so the idea is and remember we're gonna we're gonna focus right now on worksheet change that means when we make an actual change to the worksheet we want something to happen okay now when we make a change to M 4 through m 40 M 4 3 M 4 T that is the day view schedule okay remember that's here that's M 4 through m 40 we're saying to it we're telling Excel when we make a change we want something to happen well what do we want to happen well first thing is we want to do is we want to know where do we put this data we know we have to put the data on another sheet but what sheet and where what's even worse so we need to get that information okay to do that we need to define something and I've and I've dimensioned column and sheet here sheet is going to our sheet name in this case it's 2018 and the column we need to know what column where are we gonna put that okay so we need that to information we need that information we need to know what sheet and what column and the row we can figure out automatically okay the row is simple the first row in this is to the first row in this is four so all we need to do is make sure if this is 4 right we need to make sure that that goes into Row 2 here okay so that's very simple nod that part is simple okay so let's go ahead so we need the sheet we need the row and we need to call them okay those three things are the important information okay so the database column we need to get that database column and I've put it in m42 okay now how did we get that let's go look at that formula and M 42 M 42 okay here is the column now how do we know how do we know automatically that the 11th is bond column 11 now we could just extract the day out of that that would be an easy way to do that but I want to show you a little bit more in formulas yes it happens to be the day so the day but that doesn't work for it works for January but it doesn't work from up but we need them more more scientific just to go by the day and the month so what we want to do is I want to match okay so we can use our favorite indirect match okay indirect okay so what we're gonna do is reduce indirect we know the sheet name remember the sheet name is what scheduled year right right we're always gonna name the sheets of the year so that's how we could easily know what sheet okay so we know what sheet and we always know we always know the range the range is always going to be a 1 through n B 1 always okay a 1 right shift control right arrow to n B now why end B right look n B's empty but it's not remember leap here right if it's a leap year we need the Queen you to have that extra day available for us for example if we go back a 1 change this to year 2020 which is a leap here right when we go back again control shift right arrow and let's go ahead and maximize it you'll see now NB is full because that 2020 is a leap year so we do have to account for that and we can do that very simply all we need to do is simply that the way we did the month we can say if the year of na plus 1 is the same year if the year of na plus 1 is the same year as na 1 ok the year of anyone then and it but so basically what we're saying is if if this and this date have the same years then display it otherwise leave a blank ok let's go ahead and change this back to 2018 before we mess up things but I wanted to show you how you can account for leap year ok so that's very important so we always so we always want to account for that extra day every 4 years ok back into the formula we go now so we've we know our range ok so we know the range so what we're gonna say is that we need to match m3 we need to find m3 m3 is our date m3 right where is this date in this range what column matched m3 in direct and this we need an exact match right here it means an exact match the mass type is exactly okay and if for some the reason I've also wrapped this in an affair if there's an error if there's an error I wanted a blank why would there be an error well there's an error if we choose a year if we choose a year that we don't have just yet this will end up being blank and that tells us which we need to create here so that's going to be helpful saying ok if the user is choosing a year that there's no database for leave it blank and then we'll go ahead and create the year okay so that is how we get the column so we know the column the right column to put it in is an is 11 in this case okay so back into the VBA we know the database column is an M 42 we know the sheet is in P 7 another way to do it we can choose now we know that we know that database sheet let me show you another way to do that piece is it but you can also use since we've defined P 7 P 7 as the scheduled year SC year another way to write this code also accurately is simply to juice do SC year this works also ok that works just fine too so you don't have to worry about that doesn't that's another way to do it ok Fred ok and so back into so we you see that works just fine too so that's also helpful that's why I really like to use those named ranges because if you've named a range it's nice you can see exactly what's going on here you may want to use the actual cell you may want to use the name ranged you can use both ok so we've got the column we've got the sheet now we also know the row the row is the target row that means the row that we just made the change - - so we know the row we know the sheet and we know the column okay so now database sheet now the reason there's a double quote here and there's a double quote here sometimes Excel will not accept they'll think because we've used the sheet as a number and this is important if your sheet names or numbers if then it's really good to wrap it in a double quote this forces Excel to see it as a text even though we've defined it as a string sometimes not everytime sometimes Excel will throw an error but when we use this when we use the double quotes and the database sheet and double quotes it won't divert it forces it to look at it as a string and we need to make sure that the sheet sheets string in this case to an 18 cells why are we using cells and not range we're using cells and not range because both the row and the column are numbers when the row that column yeah especially the column the row is always a number when the column is a number then we use cells okay if we know the column if you know the column is a or B or C then we can use range okay but in this case both of them are our numbers so we want to use cells and all equals party value it's one simple code you see we know the sheet okay we know the row and we know the column okay equals target value and then this little bit of code and I'll show you this in a moment what this is some this is a macro and this adds the summary and I'll show you why we need to do that when we make a change when I make a change let's go ahead and choose an empty day when I make a change David David's okay I want that change to show up on the day right away so that and if I make another change I want that change to show up also on the day so that macro and I'll walk you through that in a moment we'll add that summary to the current day so that's a really helpful that way changes you may show it up right away okay so back into the code we go okay so we have another one if there's a change to anything on P 457 then load the month and basically what what I want to do is I'm saying if there's any change here we better reload that month okay reloading the month what does it do it clears everything out and it loads all of the data for that month one more time okay so if we change this to Monday right we got to make sure we clear everything out right and load it again because we need to make sure that you know it's going to change everything okay so now that's important so all we've done is let's go back to Sunday so that just reloads the data and I'll walk through how that works very shortly okay so that's an important step also we want to reload that month all right next up we have some code on selection chain you remember selection changes simply when we select a cell we want things to happen we don't even need to make a change when we select a cell just selection we want things to happen and in this case when I select a cell here I want to load the schedule so when I select anything from d6 through J 11 and also d12 through J 16 etc etc I want to load the day so let's go ahead and see how that's done so here this this bit of code really helps us in this case if the target count is greater than six and you'll often see in my code I'll put this at one but in this case because we're selecting multiple cells in this case right look we're selecting five different cells right five different ones so if I make then the reason I put this good in is when we select this if I don't have that code in there it's gonna cause a bug let me show you what that and what I mean by that okay if I comment this out okay if I comment this out and then I go ahead and select if I do that again it's gonna throw a bug you see so that's what you're gonna find that a lot with your doing code and so it's important that we say hey if it's more than six exit the sub okay so that's why I have that in there so it's important says if the user selects more than six then please go ahead and exit this up alright so if this is basically we're saying if the user selects any of these cells d6 through J 10 d12 - J 16 etc etc nothing then we'd do some things we're gonna do three things okay first of all what I want to do is says if the target offset value goes Duncan let me show you what this line is okay we know that the target is this right but offset means something around the target offset right if we do offset column -1 it's going to be this if we do offset row minus 1 it's going to be this if we do offset row plus 1 it's going to be this and if we do offset column +1 it's gonna be this so offset helps us identify specific cells around the cell your selected and what I want to do is I'm gonna say is if we've selected a cell in which the one in which the cell above is blank right if the cell above above above is the keyword is blank don't do anything okay I don't want to load a blank day right I only want to load the actual okay so let's go ahead and sit so how did I code that as it says if the target offset and remember the first the first is row offset row and then column okay call them we're gonna keep the same column so that's zero there's no offset so if the offset that means the row above the value of that if the target the value right above is does not equal empty then do something okay if it so nothing's gonna happen nothing is going to happen if it's not in Tempe okay then then basically if it's not empty then what we want to do is we want to put that day we want to take the day and put it in there okay so basically what I'm saying is let me go ahead and go through that again for you if if this is not empty then take this day here and put it here remember this is the day right this is an actual date so we can put it here they're just formatted differently here and here they're just formatted differently okay so it says m3 equals whatever this value is okay so that's how I've done that equals target offset one row up right if we're gonna do target value that would just be the actual cell we selected we don't want that I want to say the cell above the cell right above right above put it there so that's how we've done that okay and then we're gonna say load day and I'll go over that macro with you and basically that macro is going to take all of the information and loaded here okay it's actually going to take it's gonna take all of this information right and simply copy it here that's all it's going to do it's very simple right so we know what load days and I'll go over that and then another thing is b7 equals the target address okay and I'll show you why I've done that what I want to know is I want to know I want to put here what cell I've done okay and this is gonna help us when we load the summary we need to know what cell to put it in okay so basically I have to know what cell we just selected so I want to put that here these seven equals the address you see a 18 so it just puts the address right in there because that's going to help us because when we add when we add a point let's go ahead and click here when we add an appointment I need to know what cell to put this back in I need to know what cell to put the summary back in so this address is gonna help us know what cell that was in alright so that's it for the on sheet macros on our sheet okay now we have the macros for the sheets ad okay so let's go ahead and go over those okay let's go ahead and show you how we have done the macros on this and a lot of these macros use the same variable so I've dimensioned them at the top here we have the sheet name and I have a scheduled summary which we're gonna use for this text of the schedule summary as strings last row of the scheduled row scheduled column and we have the starting column and end column that's gonna help us and I'll show you why we'll use that when loading the month and the database column okay so the first thing is remember when I spoke of when when we when a users going to use a year and they've selected a new year that that's not currently in the database and remember each year is stored we want to create that database right away okay and a macro does that you see just happen right there and it's very fast so how do we create that well this particular macro does that for us and first all we need to do is first we need to define the sheet name right and remember the sheet name is going to be here we've used the named range remember that p7 okay here remember that back on the schedule our sheet name it's here scheduled year p7 okay so we've defined that so we know what we're gonna name the new sheet and we know the name and all we have to do is click sheets add that adds a new sheet and then it automatically activates that in your sheet so it's automatically the active sheet and then we're just gonna change the name of that sheet to the sheet name okay so that's simple and now our fixed copy make sure in your code don't remove the 2018 because I'm using that I'm gonna use that I'm gonna copy 2018 okay so you don't want to remove that one you can remove anywhere else so basically I said okay I'll copy everything from just the top row of 2018 and paste that into our new row in our new sheet okay so basically all we're doing and let's go over that all we're gonna do is take everything in the new column all we're gonna do is take that and a one through n b1 copy that right copy that copy right go ahead in here right paste it paste all right right click paste all okay paste everything and then I want the code to take this and just put in one one two zero two zero okay that's all the code is doing okay so just how that I did it manually there so then we're gonna see a one the new sheet a1 equals one one and the sheet name that's it because every other date is based on a formula every other date right equals a one plus one equals a right everything else is based on a formula so as soon as we change that in year everything else changes okay you see everything else changes so it's really simple it's really simple to track down I've done a lot of scheduling and so I've really simplified this to just make it so it's easy for you okay and then we're gonna just clear that cut copy most you know how when when you copy and paste something right copy right paste and paste right you still have those dancing ants around right cut application cut copy mode equals false gets rid of that okay just like double-clicking it gets rid of that so that's why we have that line of code application cut copy mode false it gets rid of those and I've included a line but I've commented out you probably want to hide it once we created you might want to hide it there's known there's no need for this to be visible so this when you comment this out simply remove that first character all right room call it out it'll automatically hide that sheet okay cuz you may not want to see it and then it's gonna go ahead and activate back we want to activate the schedule back again okay so we want to activate that so that that line of code does that so that's how we add a new sheet it's quite simple okay next is we want to add the summary and what is add the summary again okay what we want to do is basically what I want to do is I want to for every day I want to create a summary okay let's go ahead and back to 2018 where we have data okay and so I want to create a summary and I want to put that summary in the cell here okay and if I make a change I want that summary to update okay so this is the summary okay and so I want to create a text of that okay so how do we go ahead and do that well the idea is basically what we're going to do is I want to take everything every appointment in here and I want to create a single text field and I'm going to put that right down here I'm just gonna store it right down here I'm gonna use row 40 because I know that row 40 is not part of the schedule I think the scheduling stops at around 38 right because on our schedule we only have a total of we only have a total of 37 rows so if we go down to 37 right it's gonna stop at ro 38 so I can use this row it's not going to it's not going to be used so we can use row 40 so I've elected to use row 40 to store our summary okay and I want to make sure okay let's take a look at let's pick a day on the eighth okay there's nothing here okay nothing here in row 40 and let's go back to the schedule and click on the 8 here okay the eight where there's nothing and I want to add some appointments and what I want to do is basically I want to include dude some data here and then I wants you so you see how that summary and if we look back here back on the eighth right so we have the three appointments and now we have a summary the idea is I want to take each appointment I want to take what time we know what time and I want to add it I want to add the time and then I want to add the name then I want to go to the next row and I want to do that for every row that has data okay so that's the idea so now we're going to show you how we did that in VBA okay we've defined the sheet name as always we know the schedule column and 42 we went over that and and then a schedule summary I'm just going to ensure that it starts out when it starts out it's empty okay you generally don't have to do that but since we've defined it up here it's important to clear it out but if if we if we dim dim schedule summary here then it's not necessarily okay but we've done it up there because I've used it in multiple I may use it in multiple macros and then you want to make sure it's empty okay and then what we want to do is we want to define the last row okay the last row of data up until 41 so basically what that is is I want to know the last row I don't want to go through every single row until the end right I want to only go through the last row if the last row of data is 14 I don't want to go beyond that so I want to know the last row I want to make it as fast as possible okay so let's go ahead and let's go ahead and update that so that we can know the last row all right and let's go ahead and do that and to do that let's go ahead and show you how that's done in the variable so first of all we've said m41 that means everything above this I want to know what the last row is okay everything above the then what is the last row of data for example in this case it's 14 right starting here starting here what is the last row okay because there's data here right there's day here so I don't want I don't want this if I choose if I choose a row after it's always gonna say for I don't want that so we start here from here what is the last row okay in this case it's 14 so last rows 14 so now we're gonna say from 4 to 14 from 4 to 14 from 4 to 14 in this case 14 right I want it what I want to do is I want to get I want to first find every row with data if there's something here get the time get the information and move on and then go to the next row go get the date get the time get the information and go to the next row so we're gonna create a loop to do that okay from schedule from 4 to the last row the schedule summary equals the schedule summary the reason we do that is because we want to keep adding to it and adding to it and adding to it right and what we're gonna say is if the M is not empty if this M and scheduled row value is not empty right if there's actual data then do this then one at the time and here's the time the times in L okay the time is you know but you know times are just times are just values they're numbers but we have to format that into something we want to see write times are just numbers but we need to format this so we've wrapped that in a format right I've wrapped it in a format and let's say and let's take a look at this so I've done a.m. p.m. but let's say you wanted military time right just clear that out clear that a p.m. right and now watch when we double click Lisa right and then it goes to where is it what are we on the 8th okay let's do it let's choose a day we can see from here ok let's go ahead and put in double click on that ok so basically it updated it to 1300 right try it again 1500 okay but now watch when I change that back to am p.m. when I change that back control Z control Z and I change that back to am p.m. now let's now touch double click on it it'll update it so now it's updated right so that format is going to change so you should use whatever time format you you're comfortable with or whatever your users like okay feel free to change this to whatever format so it's gonna rap it's gonna wrap this value in a format and I want to put a colon and a space right a colon and a space and then the appointment and then this is a new line this sends it to a new line okay so that means that I want a colon your colon and a space and the name and then a new line so I want to do that for every appointment right and if there's a lot you see look this one has a lot of appointments all right so it's all there some of its just hidden we can only accommodate you know five in your schedule you can accommodate more if you want to make your calendar eight ten lines go for it you know all the same principles apply for our purposes we have a small screen let's just you know continue but everything's gonna be here all appointments even though they may not be displayed all right so let's continue on so we've gone through that loop and we created this all of this and then once we do that what I want to do is I want to take this text which is all the information and I want to put it on the 40th bro of our current column on the fortieth row okay I want to put it right here 40 I'll put it right here row 40 okay I want to put it that's why everything's here and it doesn't really matter what it looks like here it doesn't matter if it's not formatted this is our database so we don't we don't care too much about what it looks like there that's why there's no formatting at all and there doesn't need to be so we've put that on there right and I also want to say okay look now here's but now now that we have it here but we also need to get it on our schedule right we need to get it here right but how do we know what cell to put it in how do we know this is the one remember when you select a cell I've taken the address and put it in b7 remember that b7 now we know what cell to add that summary to don't we right b7 is where we're gonna put it so we say the range of b7 right b7 is the cell address we know where to put it now equals schedule summary so that has how we know where to put it so that's how that works all right great so let's move on to the next macro all right you still with me stay awake don't fall asleep all right let's continue on I'll know if you're sleeping let's go on to loading month now when we want to make sure as when we load a month we want to make sure that everything gets cleared out first before we load new data we also need to know where does the month start you know in January it's easy but where does February start where here's me what column does may start well may starts on May starts on DQ what column is that equal column let's take a look you go column may starts on column 121 where does it end right well it ends on May 31st right equals column right so I need to know on one fit it starts right I need to know where it starts and where it is right 121 to 151 so I need those two numbers those are important for me right so because I need to have that macro start at this column get all the summaries get all the data in row 40 and put it on the calendar okay so those two numbers are critical we need to find the starting row and so we've used that in the formula right so when we click let's go ahead and I'll show you how that macro room works March April May okay remember 121 151 we need to get those numbers we can do that with a formula okay how do we get those those column numbers the beginning and the ending column number that's very critical so how do we do that let's go ahead and show you how we've done that and basically all I've done is a match we need to find okay the month number we know the month number selected five and when we go previous month I'll show you the macro that puts that number here mmm all right so we need to find the month number we know we know the sheet the scheduled year we know the month the month is here and we know one it's the first day okay so what I'm gonna do is I'm gonna put a match I want to match that first day and I'm going to use indirect so it says where is the what column is it in this indirect scheduled year where is it so all we're gonna do is use match to find that column number okay match is going to help us out and so we've used match to help us determine exactly what column and we've also used match and in math and in math we've taken the same month but this this end of month this gives us the day this gives the last day of this month the year the month and one this will tell us what the last date is so it's gonna say okay match that last column okay and that's really really important all right so we know and it's gonna say matches where match it in this and this this is the this is the value that we're matching this is the value and this is the range what is the range was the year because that's the sheet a 1 through B n so we know where we're gonna find it so that formula helps us determine the lat the column of the last day of the year so now that we have the starting column and we have the any column we can run from one column to next and get all the data we need to fill up this calendar ok so let's go ahead and walk through that in in the VBA so first of all we're gonna check we want to make sure that for some reason that if B 5 is 0 we need to add a new sheet now why would we want to do that B 5 if it's not found what if it's not if there's an error if there's an error for example let me show you what happens ok we're on schedule 2018 okay and look there's no 2019 we have none but if we continue down right if we continue let's click here let's go ahead click here June July August September October November December ok we're on the last month of 2018 right if we click the next month it's going to be 2019 but we don't have a schedule for 2008 but I want to create one okay so that means it's gonna say it's not if we click again right it's not gonna find it you see how quick that was okay but basically now it's there you see now it's there so what I did was that's here's what I did I said hey if there's an air here if this is not found if there's an air that means that the column wasn't found if there's an air I want you to create the sheet and then go back so that's all we do dishes so if B five value is blank then add the new sheet okay let's go ahead and back in there then add an issue we already went over that macro here okay so that's just to check okay if we still need if we need to add that database let's go ahead and add it in now it's been added sheet name equals the schedule year we know about that database column in this case that we want the first column okay the first column d5 value remember let's go ahead and show that b5 is the first column b6 the last column okay let's go back to our may so we can see that all right but it's low there's ways we can make this faster but I think it's gonna be pretty good and all right so here we are 121 and 151 that is our May okay and this line is going to clear out any any values in the calendar okay we didn't clear out the whole we want to make sure we don't clear out the days so it's good basically gonna take any of these and clear any data that's in these cells it's going to clear those out so that's what that does and then we're gonna say for the scheduled row six to thirty six step six so we're gonna have two loops here two loops okay so and I'll show you why we need two loops first I want to go call them I want it I want to on this row right on this row here I'm gonna go one two three four five six seven okay then I'm going to go one two three four five six seven so the first loop is two from this row stay on the first row and then go one two seven columns and then on the second row also again so that's two loops okay so we're going to go through two loops the first is actually so the first loop is the row six is the first row okay then we're gonna go calms four to eleven why four to eleven what is that you ask okay let me show you what that is column number okay four to eleven column equals column column just type that in correctly okay column 4 right to call them eleven so we're gonna go from four to eleven okay four to eleven actually should be four to ten let me change that four to ten okay four to ten so we're gonna go through all those seven and then we're going to we're gonna say basically if the cells will select a minus one value equals empty then go to know so basically what we're saying is that in this case if this is empty right if this is empty skies are calm minus one right then don't worry so basically I want to skip any time this is empty I'm gonna skip it I don't even want to bother with it okay I'm only focused on the days of that month okay so if the schedule is the scheduled row minus one them just a robe of equals empty then go to next column I'm just gonna skip that okay otherwise the cells of the scheduled row in a scheduled column right now the equal sheets right remember it's always on wrote the summaries always you know forty and the database column so we're gonna loop through that basically all we're gonna be doing is we're gonna go go through each we're gonna go through each summary column right on for each one and add the summary add the summary at the summary at summary.com [Music] and okay that looks good okay so it runs through this and basically what we're gonna be doing is the reason we've out of database column is I want to make sure that we're starting out with the first column we really don't need the last column too bad but it's okay it starts out of the first column but I want to make sure that each time we add a day we go to the next database column right each time we add a day one column we need to go to the next one and then if so we add one database so that it keeps up okay and that's how we handle that so that's how we load the month okay and now how do we load the day okay remember when we select on a month remember we have a macro called load day right when we select let's go back to January we have some data in there okay when we select on a day we want to load that details here right when we select it we want to load it so let's go ahead and see how we did on that macro loading a day now the reason we did this remember there's an important thing when we remember when we make a change here right Fred we want that change to be saved right but also when we load a day there's also changes here right right every time we book but what we do is we say we only want to save those changes we only want to save those changes when we're actually making this kind of a change not this kind of a change okay so what we're saying is when we're loading a day don't save to the database because it's already in the database you understand so the reason is when we load a day we mark b2 as true and then when we're finished loading that we mark b2 as false and the reason for that is when we're when we use this macro right remember this is the macaron sheet where we actually save the information to the database right so there's a check in here it says only do this if b2 is false okay that means don't do anything about saving to the database when we're loading the day okay that's just ignored when we're loading the day ignore these changes okay so that's why we have that line of code in there that's very important otherwise things go a little bit crazy all right so that's why we set this to us so loading today we're simply finding the schedule sheet right the schedule column we know the schedule column right we know the sheet name here the column we know the column so all we're doing is we're gonna clear the current day in case there's any values we want to make sure that we that we clear this day we want to clear that okay that's important so that we're ready and then we're going to basically all we're doing is we're going to find we're going to find let's go ahead and go over that again we're gonna find the column okay and then we're going to copy this gonna go this copy and then paste the values we don't want to paste formats so just values and that's all so we can use that as a direct instead of copy and faced it's copy paste it's faster if we do value to value M for T value equals the range of the sheet name okay the range because we're going right sheet name we know cells are starting at Row two going to row 38 okay and this is you'll notice this is 36 rows okay and this is 36 for us when we're going value to value we need to make sure it's exactly the same number of rows and columns okay that's very important otherwise it'll give us some kind of heirs okay 36 rows one column 36 right from column 2 to column 38 I'm sorry from Row two to Row 38 okay also also 36 roasts on the single column okay so all we're doing with that line of code is we're just saying we're saying here everything from four to forty four to 40 it's equal to everything from two to 36 okay that's all we're saying so that's what we're saying that's exactly what that line of code is does it basically takes all those values and puts it right in here so that is how we use that for the code okay next we only have the last two macros which are previous month and smug okay I've created two shapes on the sheets as you can see right next month and these are just simply arrows that we've created from shapes and then I've assigned macros to those okay previous another side those two macros let me go ahead show you how those macros work in the VBA model here and in previous month what I'm gonna say is when we're going back and you remember B 4 holds our month number okay b4 holds our month number which is here okay b4 is here so this holds our number and basically what I want to say is if this is one if we're in January and we go previous one we need to make this 12 right we need to make this 12 and we also need to change the year we've got to go back one year right so when we go a previous month one boom this goes to 12 okay this goes to 2017 and if for some reason we have not had a 2017 database yet we united to create that too okay so let's go ahead and still how that how that's done load month okay so it says if B 4 equals 1 then B 4 equals 12 and P 7 equals P 7 minus 1 it means this reduces the year if it's not one that all we need to do is reduce the month from 1 and load month and you'll see load month also inside the load month we check right remember we check you see we just went to 2017 right but there was no month so in that we were able to add the new sheet in that macro all right and so that's all that macro does ok because everything is based on that month number next month the same thing if it's 12 right if the current month is 12 then make the next month 1 and then also add a year at e'er right so that means when we go back into the schedule right and we're on December and we click and our month numbers 12 we want a 1 change this to 1 and 2 change this to 1 more year okay so that's how we do that on that so that's very very simple and then also each macro we want to make sure we load the month okay remember the macro clear out we just went over that macro so that is exactly how that's done and that's how you create an extremely powerful and easy you schedule for everybody with the power of Excel and I hope you like this training sorry it's a little bit longer than most but we had a lot to cover here so I'm really glad you stayed with me the whole time please comment below whether this is on YouTube or Facebook also please do share it I always appreciate that and thank you so much for joining me have a great day [Music]

Channel: Excel For Freelancers

Views: 176,039

Keywords: Excel Scheduler, Create A Calendar in Excel, Excel Calendar, Excel Appointment Scheduler, Excel Scheduling, Schedule Appointments with Excel, Excel Contact Scheduling, Create a Calendar in Excel

Id: IWlCloFiWw4

Length: 71min 18sec (4278 seconds)

Published: Tue Jan 23 2018