Email invoices we're going to also be able to quickly create an invoice based on an appointment simply by clicking invoice it's going to automatically create an invoice or it will view the existing invoice if an appointment has already if an invoice has already been created for that appointment we've got patience i've got the ability to have billing history we can view invoices from billing history we can select previous appointments we got drag and drop scheduling we can view an appointment on selection we can move appointments to a different doctor.
We've got dynamic doctors navigation and a whole lot more this is gonna be a massive training something for everybody so i hope you'll stick with us i just asked a few things all i need from you is just go ahead and click that subscribe button below and of course don't forget to click that notification icon bell that's going to ensure that you get these trainings and of course comment below let me know your thoughts ideas applications like this get built from your suggestions this was.
Highly requested a clinic manager be able to use for hospitals doctors small clinics large clinics whatever multiple staff clinics so we're going to be able to share that with you but it comes from your suggestions i respond to each and every comment personally on our youtube platform as well as well as facebook and the other social medias so i hope you stick with us if you like this workbook it's absolutely free all you need to do is click on the link down below in the description and we'll get that sent over to you either with your email or facebook messenger however if you would like to support this channel these videos are free but of course i always appreciate your support a.
Great way to do that and to get something really special is to pick up my 250 workbook pack that's 250 of my best templates in a single zip file and i also optionally including the pdf code book so you can study the code learn it and become an incredible application developer that's my goal is to make you successful with excel and i'm here to do just that all right let's get started on this training we'll go over an overview and then we'll get into the details because there's a lot to cover grab your beverage of choice whether this coffee tea or something a little bit stronger and.
Have attachments and each individual tab has a specific patient and then the patient id i want them in separate folders so when we add a particular attachment i want to want to be able to preview that attachments and i want to make sure that that attachment gets saved in the individual folder okay so we need a separate folder for that scheduling we want a dynamic starting time we may want to start at different times our schedule is based on time we also have intervals right if we change that interval notice if i change it to let's say a 15 minute interval right and i go back into the patient manager notice it's now 8 15 if i refresh the schedule you see is how these now are longer right they're longer because each row is 15 minutes as opposed if i change it back to.
30 minutes here right they're going to be shorter when we go back and we refresh the schedule you see everything's going to be a little bit shorter okay so keep that in mind that is going to help us out so it's fully dynamic schedule right so that means a one hour appointment from 9 30 to 10 through to 10 30 is going to be two rows so we want to be able to have a fully dynamic in our admin screen a default appointment item a default appointment item so we can so every new appointment can have a default in case you want to use one what are the scheduled staff right we have five up to five different scheduled staff available inside our patient manager.
Again we have five different staff so we can set up five different staff right here inside our patient manager so we have that in the admin screen payment terms are going to help us on invoicing if we schedule an invoice we want to do in 15 days we'll put a net 15 and that's simply going to mean as we add our net 15 we want that due date automatically created just as it's done here as we either a adjust the invoice date here or we change the terms okay so that's going to be automated and that's going to come from the admin invoice settings we want to be able to charge sales tax or maybe not we want to set that default sales tax we also want some footer messages right.
Our invoice can contain several footer messages we've got thank you for your business however if i were to change that footer message here we want to make sure that it's also automatically changed down here inside our footer message so we have those footer messages inside the admin screen we've got intervals this is going to help us just determine basically intervals of our scheduling durations the duration appointment we need to list the times right the times of when we can schedule that we don't want to schedule it before our start time so that's going to be important invoice status and our payment types okay inside our patient manager we have the ability to.
Of course add a patient create a brand new patient if we want to or we can load an existing patient just by selecting that patient here if the patient has a history of appointments of course those appointment histories are going to show up here if they've been invoiced they're going to show up here if i want to select a previous appointment history all i've got to do is select here that appointment is going to be loaded up here if i want to add a new appointment i can click new appointment and of course we can make changes to an existing appointment right this was on 7 10. so i would go back to july 10th we can see that appointment right we see that that if i select an appointment here and i move it over because there's a conflict i can do just that it's automatically going to get saved and it's.
Posts Related:
Automatically going to be displayed wherever it is so if i select this we're going to have that
Particular patient load up we're going to have that particular patient appointment highlighted and also we've got that to go on so we've got some navigation on that i've got a billing history the history of the invoices if i select on that right i can create i can view that invoice but if i want to create an invoice automatically let's say i create a new appointment and i'm going to create it on let's say 7 15 july 15th and i want to assign a particular staff to that let's say dr matthews here and i want to give it a visit reason let's say leg pain and let's say the.Appointment is going to be evaluation and we set up a time at let's say 9 a.m and the duration of 1 o'clock we'll leave the notes blank right so it's going to be 7 15 if i save that appointment right and then i go to i'll browse for it on the 15th i select that we're going to see that that automatically dr matthews and lake payne has already been scheduled here for fred fetters that evaluation if i select on that however maybe the appointment is done we want to create an invoice for that we have it we've got some invoice items if we take a look inside our appointment items here we've got different appointment items and we have our item id item name uh description and the.
Duration the color and the cost so we have all that information going for us and that's going to help us out because when i want to create an invoice for that all i need to do is just click invoice that page is going to be loaded that evaluation is going to automatically be created right a brand new invoice and all i need to do is just click save and it's going to automatically be saved of course i can add in a footer message if i want to i can add in a status if there's any payments i can add those in as well and that's going to help us and then just click save so we can really create an invoice just based on one click and it's automatically going to be created here all right great so we've got that lot to show you on that of course the scheduling.
Navigation we have a staff list of staff here that's our doctors and staff list here we have our patients a list of patients right patient information which we'll be able to add and of course we've got our dashboard here which we're going to get into we've got our dashboard it's going to be broken up into two parts basically we have our appointment information we have our number of appointments by staff here we also have our appointments by type right we want to know the appointment type let's change this to white i think i like that white font a little bit better and bold right so we've got that we also want to know that our july appointments the monthly appointments on a per day basis and then it's broken up into our sales sales by item sales by staff and sales by month so we're going to cover all that and a whole lot more we'll be.
Going over every single detail with you in this training we're going to start with our patient manager section we're going to be able to add new save and delete patients right we can simply load existing patients by selecting them here and of course all the information along with any file attachments appointment history invoices will load automatically when we have that okay so let's get into some of the code that's going to allow us to do that and then we'll move on to some of the other appointments and how we're going to do that and how we load and display appointments okay so the first thing what we want to do is when i noticed that when i made a change here under e4 the patient loaded right so i've got some information and i've got that's going to help us do that some code but before we get into the code we want to certainly understand some of the formulas that are going to help us guide us and we have some of them right here in the first.
Two columns a and b so the first thing what i want to do is i've created some formulas and some named ranges inside those formulas that will guide us along the way so let's take a look at the name ranges that we're going to be using throughout this application and there's a few of them right we've got one that's called the appointment date i want to know what date was the appointment on if we take a look here that is going to be focused on all the appointment dates this is our appointment database all those data center forms are scheduled on i've got an appointment id right there's a unique id for every single appointment we have assigned staff the assigned.
Staff for every single appointment is located here right each staff doctor or staff we can assign i want to know the start date the start time that could help us out and of course we have the data all the data is going to be associated with there we have appointment item id now notice that we have individual items right appointment items will also be able to assign colors to those individual items those colors will be reflected on the schedule we can quickly add it with a single click and we'll show you that right so items appointment items have unique ids and we can create as many as we want they also have unique names so we create unique names as well i've got an attachment folder.
Right that attachments folder is the named range and it's located in the admin screen that's going to help us add attachments right booking type we don't need that it's something that's unnecessary and we don't need this here okay so that's from an older training okay we also have the calendar months criteria that's for our pop-up calendar we won't need that display type is important right what do we want to show let's take a look at this here inside our admin screen we have display patient names now we may want that let's close out of there so i can show you that real quick now we may want that to show our last names first or first names last right depends how we want to display if we want to display our patient's first name then last name i want that reflected inside here so automatically our drop down list is now going to show fred freder's right however.
If we change it back inside to last first the way we had it we want then our in our patient manager reflected to that last name first first name last so we have that here okay so i want to make sure that we have the ability to do that and i'll show you exactly how we can do that automatically okay so that is in our that particular is called display type display type as we continue in our name manager so now that we've covered that i also want the duration right the duration of those.
Appointments in a named range that's this list of durations here and also if we move beyond the
Extract those are only used for advanced filters we had our footer message inside here we've got our footer messages here and we also have our footer message name the names of the footer messages and the footer messages themselves we have a very specific interval remember we change the interval and i've got an interval that's in a decimal format and that interval 0.020833 is very specific to the 30 minutes right one day is one so half a day would be one divided by two.And if we want to know the number of hours would be 1 divided by 12 30 minutes would be 1 divided by 24. 24 times 30 minutes goes into a single day that's how we arrive at that decimal we also have an invoice appointment id right every type of time we invoice it i want to know the appointment id was that important that means if we create an invoice i want to know what appointment that was attached to that way and that's really important and why is that important because if an invoice got created i want to look at the appointment the original appointment that was from so i can.
Click view appointment and it's going to take me directly back to that specific appointment with dr matthews and lake payne for fred fetters i want to be able to know that so it's automatically right so automatically for that that dr matthews fred fetters it'll go back to the original appointment so it's two ways we can go from the invoice back to the original appointment or we can look at the particular invoice and then we can also view the invoice right here so we can do that so that's another way that's a different invoice but we can see how we can do that very very easily so we want to make sure that we have that ability inside there okay so back in.
The name manager continuing on where we left off which was with the invoice appointment id that's why we want to track appointment ids on a per invoice base we also have the invoice date here we have an invoice id and that is unique id per invoice and also i want to know the patient what is the patient name on a per right what patient was that the total of the invoice right want to know the total that will help us out for some that's not correct that would be the invoice status we'll go ahead and update that the invoice total is located in column l so we're going to update that right there okay so we'll be using that there for.
That and then what i want to do is i also want to make sure there we go now we're on the total and then invoice data we have a of course we're going to turn that into a pivot data and we have the patient's email that was going to show us out and let's see a few others that are critical on this patient name and patient id will be using those a lot so a unique patient id and patient name those are critical everything else is is not as important right terms we have a tax let's go over that tax option right tax option is yes or no right tax tax tax percentage.
Is a percentage tax option here is the yes or no so we know that we can use that in named range and we also have terms what are the terms right the list of terms that we're going to be needing and lastly the times the times are here so we know all the times we're going to use those in the schedule that is certainly important because we need to schedule our appointments based on a list of times so we need to drop down data validation for that now that we have a basic understanding of some of the named ranges that we'll be using in this workbook today let's go over some of the formulas how we get when when i search for a particular patient i want i want to know the id of that patient right the id of the patient selected is going to be right here.
So what we're going to be doing is we're going to be using an index match now it can get a little bit tricky but it's very very important that we understand when we go into the patients here we see a list of patients right here we have a list of patients here we have a last name and a first name and that's great but what i really want is either to show first name last name or last name first name right so i want a separate list and we've created a macro we'll get into that macro but i've created another list right here with that full name and that full name is based on a formula if the display type remember that display type was either first last or last first if it's first last what we're going to do is we're going to combine the last name and the first name and bring it down so what i want to do is i want to create a brand.
New list here and i want that patient id to be brought over and i want that full name now this full name of course is going to be dynamic based on our formula here so when i create a unique list here of all of our patients and i can bring down the formula here i can then sort this list automatically based on alphabetically and i like that because i don't have to touch our original data so i can combine the first name and last name or last name and first name here i can go by and i can also have a patient id so i've created some named range for this.
Very specific range here let's look in that here just so we can identify that and that is called patient here patient full name here and patient full name id so that way when i see the full name i can know the id that's attached to that full name because here in our original data we only have last name and first name right so we don't know which one so creating that separate database and there's just a macro that's going to load this information here and then a macro that's going to bring this formula and bring it all the way down only for the necessary data so we have that so.
That's really going to help us out because when a user selects this i want to know the id that is associated with the one they've selected and we could do that called patient search id here so we're going to use if error we're going to index that patient full name id right that's what i want to extract that full name id and i'm going to run a match based on what the user has selected in e4 and that's going to be the patient full name and i want the first column in one now of course that data validation that we are using is also the patient full name so when we look in the data.
Validation here we see that that's patient full name right that's that patient full name and that way this list changes right last name first the first name last let's go ahead and show that to you and there's a macro that's going to do that for us so right when i change this to first name last and we look right back inside our patients here we see that now these are now first name last name right so we're doing that automatically through a formula kind of helps us and it makes it look a lot easier okay let's stick with last name first name so what i want to do is i want to know the associated id for fred fredders i want to make sure and it's going to extract basically all that that formula is doing is looking up fred fretters looking here and just pulling out the patient id.
From here so we're going to have that so back inside the patient manager i also want to know the patient id this one is going to be whatever is loaded right so this is based on search when i load it it's going to take this id and it's going to place it directly here right so this one's based on search this is based on the visible one right here which one we're working with right if i clear this out here we still have the selected patient id here i also want to know the row that's been associated with that what row is associated with that how do we determine the row now if we have our list of patients i want to know what row fret is on if we take a look in.
Here in row 6 we see that that fred id number three is located on row six so we want to be able to determine that and we can use a match statement for that based on what is in b2 here based on it a patient id we're adding three because our first one starts on row four i also want to know the next patient id let's go ahead and add that in there in case we have to add that how are we going to do that we're going to do if air in case there's no data at all we're going to use the max formula and we're going to use the patient id right the patient the regular patient id is sufficient we're going to add 1 to that because i want to know the maximum ball if there's no data i'm going to put one like and that's going to give us the next available one so when we add new.
It's going to take whatever patient here inside whatever patient id here and we're going to simply replace it directly inside b2 and that's going to put it there great because i want to know the next one of course if we look inside our patients we see we've got eight so we know the next one is going to be 9. okay great i also want to know the selected date on the schedule as we move this day around i want to make sure that i have a specific date and i'm going to place that date right here yes that date is also right in here located here but i want to maybe customize this sometimes and format it so i want the original date here it's easier for us to change here so i'm going to put that in b5 if i've selected an appointment i want the id of that appointment located in b6.
I want to know the row of that appointment that's on the database on row 5. and that means appointment id 2 is located on row 5. where we look at our appointments and we look at our appointment id2 and it is on row five um again just like we did with the patients we are going to be matching that right we need to use that match and we're going to be adding three because our first one starts on row four we're matching whatever's in b6 that's our appointment id using the appointment id named range if there's an error we're going to put blank again i also need to know the next appointment id and we're going to use the max formula to figure that out for us just like we did with the next patient again also the invoice id if i've selected it if there's an.
Invoice attached to this i know that this has been billed out right this is where name drainage comes if i select disappointment i want to know there's no look there's no invoice attached to this however if i select this appointment or this appointment there is an invoice id how do i know that well we're going to use this formula what we're going to do is we're going to index all the invoice ids right i want to match it based on the invoice appointment id what does that mean that means i'm going to be looking up this particular appointment id 17 and i want to know what the invoice id associated with that if we look inside our invoice list here all of our data.
And we take a look inside our here we see our appointment idea 17 appointment id 17 and we know our invoice id is 20. so anytime i see 17 as the employment id i want to return 20. i want to know that there's an invoice associated with that if it is found right sometimes it not it will not be found so that means here appointment id 17 i want to know that it's invoice id 20 so we're using this formula to extract it and why is that important because if i decide that i'm going to click invoice i want it to go directly to invoice id number 20 right so here's 20 20 right however.
If it has not been created yet so let's say this one here let's find one okay this one here has not been no there's notice no invoice id right here so i need to know that when we decide we want to create an invoice for this appointment is going to be a brand new invoice when we click here the next invoice available is 52. it has not been saved yet right so we know that it's not there so as soon as we save it so what it's going to do it's going to take all that information right it's going to take this deployment item it's going to take the assigned staff it's going to take the name.
And it's going to create an invoice based on all that and it's almost ready to go if we want to add more items we can however all we need to do is just simply save it add a footer message or status or payments if we want to so it's a very quick way so we need to understand inside the macro whether it has been created or not and this particular b9 is going to let us know if it hasn't been created all right appointment request required field this one i want to know the number of required fields for appointments certain when i create a brand new appointment i try to save that there's a minimum number of fields that were required we need to make sure we add a date a staff and a time before.
Saving that and that's a date here a staff here and a time here so these three are required easy way to do that is simply to use count a for those specific cells if that number is less then of course three then we know that we need to let the user know that those are required as soon as i select an appointment and you'll see this goes back to three because those particular fields have values in it and they have been counted if i click new appointment and i add a date here we see that that's going to go to one if i add a signed staff it's going to go to two and the last required field is of course right here 9 am and now it's going to go to 3 and it will allow us to save that appointment all right great so we understand the importance of this.
Required fields i also want to know these are going to come in when we come into scheduling we'll cover that right i want to be able to drag and drop appointments very very easily i also want to select appointment and i want to be able to change the duration if i want to and then save that appointment i want to make sure that that schedule updates automatically unsafe so we'll get into this and then i also want to know the selected attachments row if our let's if our patient has attachments i want to know what row they've selected this is going to do that that's 22 right so the selected row it's going to trigger some conditional formatting and it's also going to.
Let us view it if i want to view it or maybe we want to delete an attachment we can do that oops let's fix that i change the macro for that so let's go ahead and fix that up that is going to be in the patient macros here and we have one now change the name of that patient macros right here load attachments we'll be getting into all this so we have four different modules this is the one i want and we're going to update that okay so that's going to cover rerun that so we want to be able to are sure you want to delete this attachment yes okay so we're deleting that and that attachment gets deleted and then we're going to be able to refresh the other so we can delete attachments a great way to do that i also want to know the selected invoice row if i've selected a specific invoice i want to know what row we have selected that is going to be located right here in b21.
Conditional formatting is going to trigger on that and it's going to show so i've got this one this conditional formatting is real and this one are relatively easy this one is unique and fun okay so these conditional format if we go into here we see conditional fry simply based on the row we're coloring it if you've seen these before simply based on b21 okay and also likewise for this one we're simply coloring it based on b20 here inside conditional formatting however the one that i want to show you is a little bit different right so now what i have is i've got some hidden right here i've got some hidden information here if i go and change the format.
Here and i click general here on all of them let's select all of them general right we see that we've got some numbers here now and those numbers are of course our appointment ids associated with this so if i've got some appointment ids here and i know that this appointment id is one it's brought over but normally it's hidden and i've got that appointment id right here if we find a match i want to color that line so that if i select here appointment id 55 loaded i'm going to take this id i'm going to put it directly in here and then i'm going to load this appointment information right so what i want to do is i want to have to make sure that we're running a conditional formatting based on that so how are we going to do that well let's take a look inside the conditional formatting and we're going to manage the rules we're going to take a look and it's going to be.
Two conditions under that the two conditions are and we want to make sure that b6 does not equal empty b6 does not equal empty and also b6 is equal to f11 however f in the 11 is of course not absolute it's going to be for every row as long as our applies 2 starts in row 11. notice there's no dollar sign before the 11. that means it'll apply to every row so if we see our applies to is g11 through j17 so our applies to starts at the same row and that way we can easily automatically.
Conditional formatting anytime whatever we have a match from the id here and the id here and and of course our macros now we want to hide this data we don't really want to see this data all we need to do is just go into the home use a custom format under more formats right here and just use three semicolons one two three and then it's going to automatically be hidden and there it is so it's hidden there so we don't need to see that so that's how we're using these three different conditional formats this is our sample shape it is this shape that will be duplicated in order for us to create this schedule we'll be getting into that in the schedule okay so let's give a start on this a particular patient information notice when we make a change to.
E4 we see that that patient low information loads up along with that so let's get into the macro going on that and of course that's going to be inside our developers and visual basic right i've got different modules we're going to start off with the patient macros here right up here and we're going to start off with all we'll start up all the way at the top it's a little bit easier but what i want to make sure is that we understand our course dimension variables we have last row and last results are as long patient row i want to know the row of that database the column as we loop through and the selected row what row we're selecting and the patient name as string okay when we create a brand new patient right want a few things to happen i click add new i want to clear it all and i also want to make sure the patient type if it's the default notice inside.
Our here right inside our patient we have some information here patient defaults and details and we have a default type patient type as referral if i were to change that to walk in or leave it blank but i could change it to walk in and i click brand new patient here i want to make sure that that patient type goes default to walk-in okay so we've got a few things happening here basically i want to clear the contents of all these i want to set the default to work to notes and and i want to set the default so that's all we're going to be doing inside our brand new patients and i want to make sure that everything else gets cleared out right if we've got a new patient.
I want to make sure the appointment information gets cleared out the history the billing history and the attachments and perhaps any preview that might show up i want to make sure that that's all gone so we're going to do that inside this first thing all that attachment preview remember we saw that x-ray that's always going to be given this name called attachment preview so we're going to delete that we're focusing on the patient manager now but if it doesn't exist it could create an error so we always must wrap it in on our resume next and on air go to zero we're going to clear out all of those fields associated with that particular patient the history the invoice history and the appointment history i also want to set that default text to notes d16 is.