Alright thanks so much for joining me today i'm gonna create this incredible dashboard right along with you i cannot wait it's going to be a great training i've got so much to cover in this final series first of all if you are here that means you probably have our joint our first 19 so i wanted to give you a big congratulations on that thank you for sticking with me on this incredible series next week something brand new i can't wait to share with that with you but this week we're going to complete the dashboard now of course you can create so much more in this school manager there's so much that can be added all you need to do is just click on the link down below either with your email or with your facebook messenger and we'll get that sent over to write to you.
Absolutely free of course if you do like these trainings and you want to create your own i've got an incredible promotion in our mentorship program i'm going to show you step by step how you can create your own applications from scratch and sell them for passive income in fact right now i've got an incredible promotion for just seven dollars you can get the first five weeks of this program just seven dollars that's going to be over eight hours of training.
We're gonna include five weekly modules and of course we're gonna get you all the content to get you started that's going to be a temporary promotion so make sure you click the link down below seven dollars to get you started all right i want to get started right away this is the sample i'll be creating this i'm going to save this and close this this is what we're going to be creating by the time we get done with this training it's going to look just like this okay so i'm going to save it this is the sample we're going to close it out now i did get a little bit of a head start because we already know how to create these menu we've done this prior and this single click all i've done is just pretty much format this here because i know we're going to put in three reports now of course you can add as many reports as you want in this incredible format i'm going to show you how to do that in this training so it's going to be incredible and.
We've got a lot to cover the first thing what i want to do is just go we already know this hide sheet this is used for user security so we understand that this will keep blank i want to know the report number if i select here i want that report number one to show up okay so i want to keep that in mind i want to know in the selected student row we're going to use this when it comes to report cards right i want to know what row when we select on which report which student to display those report cards i want to know there and i also want to know the student id when we select it that's going to be for this report card this report here so we're going to have that i also want to have a terms right i want to have a drop down list of terms so i want to put that here then i want to have the text of that term i want to know the from.
Date what date is it from for example if we're going to do 5 1 i want to know that from date and if we're going to do the 5 31 i want to know that to date so those are these dates are really important because as we change the term we're going to change that i wanted to know now keep in mind that our terms are going to come directly from the admin so when we look at scheduling actually it's going to be classes and starting we see let's take a look here classes here we go here's our terms right here so i want to drop down the list of these terms so when i select term one i want these two dates or when i select this date i want these dates to show up right so that's very important so that we can understand how we are going to be able to create these terms.
And also so now when we have it okay good so we have our terms here we can shrink that up a little bit we want to make sure it's okay here but i want to for maybe just shrink it up a little bit to the width of maybe just one keep it keep it simple okay so now what i want to do is i want to have obviously a label here but i want to do more in that i want to make sure that what link is it connected to so i want to know the select term and i'm going to make it in this case i'm going to make it b6 so that's going to so as we change that it's going to be it's going to be dynamic so that when we change that here it's going to show up 2 or the first one or 1. but now what i also want to do is i want to display what term text because i'm going to use that in report.
What is the text term 2 20 21 term 3 i want to display that here so how are we going to do that well we can use of course just a simple index and match here so first of all let's just do if air in case there's an error i don't want it to display anything just in case so if air and then we'll go up here then what i'm going to do is i'm going to index and what am i indexing i'm going to change the terms that's the form we're using that's the name range here what is the row number i'm going to use the row number is basically this right here we've already selected it so all we need to do is just click b6 and we want the column number okay and if there's an error just to show blank okay.
So now as we change this term it's going to change here so that's pretty much what i want also the from and to date those are very important what i want to do is i want to assign named ranges to those because we're going to use those inside formulas so i'm going to call this from and then dt and then i'm going to call this one two dt because i want it's much easier to use it in formulas if we understand so this is again this is going to be the from date and two day we can see that up there all right the student this will come in later and then this will come in later now i also want conditional formatting so that i've already actually added that in so if i change this to one i want to make sure that this goes right now i've added in to just.
Save us a little time but if we take a look in the conditional formatting we manage rules basically it's based on this so b3 equals row minus two right i want to show report number one but i wanted to highlight line number three row number three right report number one i wanted to highlight so how do we do that it's basically b3 is going to equal the row in this case three minus two so it's going to be one so that's the match and basically just to format it which is going to be the same i want that bold dark blue and then i want the white background so that as we select these as i select these this is going to change to 2 or this is going to change to 3 and it's going to allow us to highlight the selected report so when we add our vba in here we'll be able to do that okay great so let's save our work so far so now what we want to.
Do is when i select the term i want those dates to show up those from in two days to show up here in text so we're going to need a little bit of a text box so what i'm going to do is i'm going to click insert and i'm going to insert a text box here so the first one is going to be we'll just call this term and then i'll format that accordingly and then i'll duplicate it for the from into but before we do why don't we format this just the way we want i'm going to drop this down while we're formatting it go into the format and we'll we'll give it a height of 0.23 i want to give everything not 2 3 but 0.23 and then we'll give it a width of something like 0.5 or something just just.
Something similar okay so that's going to be it and then i make sure we want to format those so everything fits so we're going to go into the size and properties here and then just go into
The text box here and the left margin can be 0.03 and the right can be the .03 the top could be zero and zero we're going to center that anyways so we want to get it just the way we like it and then i'm going to right justify that and put it in the middle and i want a no fill on that so no fill and then we'll put a gray border around it something similar to what is already there something like this would be good okay so that's what i like i like that there and just move it around and then we'll make it consistent so i've got that a height of 0.23 i'm going to make sure that this also contains 0.23 so that everything's got the same height all right so we've got the term here.But now what i want is the to and from date so i'm going to duplicate that ctrl d and i'm going to change this to 2 and then i'm going to do the same thing and i'm going to put that here uh actually let's say from and i'll duplicate that and make that from and bring it over to the right side okay so we've got that and now what i want to do is so we've got the term from and to so what i want to do is i want to put fields in here so again i'm going to do the same thing i'm going to insert the shape this time another text box and this time i'll put that text box right around here it'll be a slightly different i'll do a white background on this but similar so in this case we will do again the height 0.23 keeping the same height we'll do the width as one to make sure that it's probably a bit too big it might be okay because our dates are going to be a bit bigger on this because we might have larger dates so how do we do that all right so let's give this a white.
Background and then the shape fill of white and we'll do the same borders here i want the borders to be that same gray so that everything's consistent here so the shape outline making sure that it's this gray okay i like that and what this is going to be this is going to be our from date so when we want it from i want to make sure it's linked to here our from date so all we need to do is just equals and then select b8 we might make sure that equals the from date it's also the from date okay again let's reset it back to 0.23 i want to make sure that that's consistent with all and i'm going to duplicate that using control d and i'll bring it over here to the 2. so now in.
This case we have again 0.23 keep resetting that it's probably got auto text which we don't want so i want to make sure that the auto text auto height auto width is set not set so we can make sure that we hit that and go into the properties we want that resize text to fit shape we don't resize shaped fit text we don't want that right we don't want that autofit there and so all right good i like that that's why it's auto fitting so term from into so let's line everything up by holding down the control and like that make sure that all the heights are even that's the way i want it.
Aligning them to the middle perfect just the way i want and i'll check okay i like that it's nice keeping this let's move this a little bit over here so that it's consistent perfect that looks really good okay so what i want to do is i want to group these together and then what i want to do is assign a name to them and also make sure that the size and properties so again line everything up here going to group them together and then the first thing we want to do is make sure that we do not size those things you just want to move it so that's move but don't size with the cells so that's good so we've got let me give this a name we're going to call it term group okay very good so now when our date changes so now when we date change we need to make sure.
That our date changes are from and two dates change here so we'll do that with a macro but we got everything set up just the way we like it okay so we're going to bring this over to the left that won't change positions we'll use that for various reports all right all we need to do is just link this last one here this one particularly i want to link that that's going to be the to date not the from date so that's it the to date is what we have there and now it's going to automatically link there and it'll change so that means whenever we change this term all we need to do is update both b8 and b9 why don't we do that now so we can automate that so the best way to do is go.
Into the developer and then visual basic okay so once we're in here of course we're gonna go inside our i've got some just some modules that i created but there's not much in them dashboard miscellaneous so what we're going to do is we have one for clear reports which is fine and one for term change so when we change the term that is when we want those dates to change so it's relatively easy so how are we going to do that well first of all we're going to dimension the term row i need to know the row term row as long we're going to focus on the dashboard so with dashboard and just to make sure you have the sheet name click the dot put the dot in here the period and we'll make sure that intellisense appears and we know we've got it if okay dot range we want to.
Make sure that b6 let's take a look b6 we want to make sure it contains a value we'll bring this up here b6 has to contain a value if it doesn't then we can't do anything if b6 does contain a value then what do i want to do well then i want to determine whatever is in a admin a y and the specific look it starts in row 15. so if we've selected the term one it's a one then we just need to add 14 or whatever is at a y and then add in whatever is in 14 plus whatever the term in a z so we just need to add that in and i need to place those directly inside b 8 and b 9. so that's all.
We're going to do right inside the dashboard okay so let's do that so we're going to place it in b8 and b9 so let's write up that inside the macro and we can get that going so if dot range b6 dot value does not equal empty if it doesn't there's nothing if it equals empty we can just exit the sub equals empty then exit sub okay assuming that it is not empty we can then assign it to a variable so we're going to call that term row is going to be equal to and we'll just put that as term row once we have that what we can do is then set b and b9 dot range b8 that is our from day b value equals admin dot range a y remember a y and also we need to add on the term row or we.
Can add it on to this term row we could probably add it on here so remember it's going to be plus 13 right we want it so here inside the admin if our first one's away 14 actually so if our first
Row is 15 right then we need to add 14 onto that so we just add in the 14 we can add it right here onto the form of 14. okay so then a y and our term row dot value that's going to be the from date we can copy that and just update that to the to date and change it to b9 change it to a z so here updating that now copy that and then bring it over here so b9 which is our two date.Is going to be equal from a z in the term row and that's going to be our two date so that's all we have to do then we just assign that macro there and all right perfect so that's going to be it let's save our work now again i'm just going to assign this macro as we change the terms it's going to shine so back into the dashboard screen here taking this particular item here this this one here right clicking that assigning the macro and pasting that macro in now when i change that term row we're going to see the dates change okay so let's look at term 1 121 to 4 1 double check.
January 21st to april 1st i like that let's bring let's format these the same the way that they're formatted and we'll just use the short date so we can okay 121.41 that's looking perfect we're going to keep it let's put it on term two because that's where most of our data is so i'll keep it on there back inside the dashboard changing that to term two it updates those dates very nicely okay so the idea is that we're going to refresh whatever report we're on when we change the date but we don't have any reports yet so we're going to create those now first one i want to do is the profit and loss report so when i select this i want that profit and loss report now where does that information come from well it comes from our transaction database this is our.
Database so basically what i want to do is take all these transactions determine if their income or expenses and also determine if they're within our specific date range right only in within the date range if that transaction date is within that date range then i wanted to determine all the income and all them and i want to list it by whatever categories they're on so for example if i want to know the total fees within that date range i want to know the total salaries and i want to know the total supplies if their expenses are income i want to list all the income first then i want to total the income then i want to list all the expenses and.
Then i want to determine the profit based on that which is the income minus expenses so what we want to do is have some criteria so we have income so we got some first thing what i always determine all the income using an advanced filter based on those two date ranges so if i create this criteria which i already created and base it on the from date so it's going to be greater than the from date notice we want these in this number format i don't want them in date format here that's exactly the format that i want those are dates based on just the numbers and i want it less than a greater than two day we've given those name ranges i've already added mint so the first what we're going to do is we're going to run an advanced filter based on this.
Criteria the second time we're going to run an advanced filter based on this criteria i only want to know the expenses in that and then what i want to do is i want a list of categories i want a list of categories that come here based on this criteria so based on this criteria i want to know all the categories then what i'm going to do is i'm going to create a formula now formulas we're going to update that formula here based on this right so how do we do that well the first thing what i want to do is i'm going to create some named ranges or update some name ranges notice that i've got now type here and category but i want to make sure that there's named ranges based on those so if we look in the formulas the name manager under transaction because i want to categorize i've got transaction amount i need that that's good transaction category that's going to.
Be wrong well not wrong but i just need to update the name because transaction category i want based on this so let's take a look at what that is inside the admin and rename it so we look at transaction settings so i've got categories here which is fine i need a list of categories but i just want to rename them a little bit so let's go back into the name manager here in the formulas and take a look at those transaction categories here transaction category okay so it's these categories which is fine but let's just update that name and we're going to call it we're going to keep the trans for transaction categories let's do categories or we just want to rename that keep it keep it separate keep your name ranges clear so transaction categories we're going to call it that that's what i like transaction category that looks good okay so let's go ahead and update that.
And then tab it over the perfect so i'm going to keep transaction now what i'm going to do is go back into the transactions database here and i'm going to create a name range based on these categories because that's what i want and i also want to make sure we have type as well so under the formulas name manager this case transaction okay we're going to focus on transaction id i'm going to use that as our format so we've got name and we've got a few others so let's create one for categories i'm going to copy this one click new and we're going to call this trans category okay paste that in except in this case we're going to make the difference of column l so i want to set that on column l not column a which is our id l tab out tab in there.
Go because i want to run totals based on these categories click ok and click close okay so now we see we have that so now what i want to do is i want to run a sum if so if i list all those categories here based on those dates i want to create a sum i'm going to take this formula here and bring it all the way down so if i put in fees or something i want that here so let's take a look i've got it but i want to update our so we have transaction amount transaction date transaction date and then we have so we want to make sure from date here we want to make sure we have from date the criteria is going to be the from date and i also want to make sure that we have those so this.
One i need to replace remember we need to update this one to the one we just created transaction category that's the way we want okay there we go and i want to do the same thing with this because we renamed it so i want to base it on that transaction category okay again so all what i'm doing is simply determining here we're going to use some transaction amount that's what we're summing we're going to base it on the transaction date and must be greater than or equal to from date also the transaction date must be less than or equal to date and the transaction category has to be whatever is in x3 this way as i add in these results and then i'm going to just bring.
This formula down here those are going to be all for our income once i have those totals then what i want to do is do the same thing but for expenses i want to know all the expense categories then i want to bring this formula down but when that way we don't have extra formulas when we recreate this report we can clear everything else out and that keeps our workbook really light because i don't have these formulas sitting here for not needed then what i want to do is i want to know the total income so it's going to use the sum of the total income and i want to know the total expenses which is going to be the time these are going to help me when i total those okay so now all we need to do within the macro is do just two things one we need to run an advanced filter based on this.
Information here and we'll let's give those a color because those are all formulas and they're fixed so let's give that a color and then we'll just give that a board i'll drop this down and then we'll just give it a border here okay so we we have that not this one here so we have that here income which is fixed i want based on these dates so our first advanced filter is going to here for income our second advanced filter criteria is going to be here so let's write that up right now inside the macro so we know we're going to do we're going to write our first macro for that profit and loss report going to first our our primary uh starting out just based on this information based on the transaction database so we're going to go back into the vba and now i.
Have a module created called dashboard reports and this is the one we're playing profit and loss but before we do that we need to dimension some variables on that so let's do that right now so we're going to mention we need the last row we're going to use that a line as we get the last row of whatever database we're working with as long i also want to know the last expense row we need to keep track of what there is as long because the expenses and income are going to have different also of course the last income row the last income row as long also if i'm keeping track of what row we're on on the dashboard i need to know what row we're on as we build that p l so dashboard row is going to equal as long angle as long and also i want to know the last result last result.
Row because we're running an advanced filter so we need to know where those real results come to okay as long also i want to know the report row when we select this will become in handy in other reports as long okay and we're going to set the report range because we're going to set the print range so we need to do that and we're going to also copy and paste it over from our staging area up into our main report error so dimension the report range as a range so we're going to use that for each of the reports so all right good now we're ready to create our report for our profit and loss the first thing what i want to do is create a staging area where we're going to build that and.
It's going to be off here so we're going to build it out inside here let's bring it let's use a starting with a a and then for each one so i'm going to bring it right here now i want to keep the row consistent my first row wherever i want to put that information is in row 4 so in our staging area i'm also going to keep it as row 4. this helps for conditional formatting so the first thing what i want to do is give it a name and that report name and we'll do that inside a4 so what is it it's going to be dynamic so because it's going to be based on the term so we're going to call this equals and then we'll do profit it's profit and loss profit and loss.
And then of course i want to do it from one from put a dash in from and then it's going to be a date so we need to set that date what is it space and and it's going to be the from date right i just don't want to put in the from date because it's not going to be formatted so what i want to do is i want to format that specifically so it's going to be text and what format do i want to sign that date and we'll just do you can do any format you want but i'll just do mm slash dd slash yyyy and then close it okay so that's the from date and what else and i want to add the to date and so and space 2 right because we want to do two another space.
And then also want to do the two days so now all i need to do is just copy this here and then just change the variable instead of from date to the to date so that's easy okay two days okay good let's take a look at that and we'll see how that works all right so that's what i want pretty much good that's going to you but i do want to assign it that our specific format that we're using that's common throughout the application and we've saved that in cell styles we're going to give it that table table title and i'm going to stretch it out probably all the way to let's say a d i want to do that so a d is going to take it all the way because i'm going to create about four.
Columns for this one so we're going to assign that cell style all the way through a d but i want to make sure that it's merging across the center now excuse me i don't want to use merge and center but i do want to use center across selection so what i'm going to do is highlight all these and then you can either right click or click here and what i want to do is inside here the alignment i want to put make sure you put center across selection okay click ok that helps us we don't need to use merge and center all right so we're going to create this create some spaces here and now what i want to do the first thing i want to do is i want to put income in here this is where i income because this is going to be fixed this word here then everything dynamics can appear here all right so the first thing what i was i want to list all of the income here we can bring this over so i want to list all the income accounts here and then inside ac i want to list all the amounts here.
So all those amounts are going to brought over from the then what i want to do is i want to put total in here i want to put total income and then right here i want to have the total then i'm going to list the expenses then all the expenses down here all those expense amounts here and then here total expenses here and then another line called total profit so that's how i want to build it out just using these four columns it's going to be relatively simple so how we're going to do that well we use that of course our macro so let's continue on with our macro and see just how that's going to work okay so here it is right here so profit and loss report so first thing what i want to do is i want to clear out anything any prior results from our dashboard so dashboard.
Dot range and we're going to clear the contents out so what am i going to clear out well the first thing what i want to do is clear out all the way let's starting out here from a6 i'm going to keep this income here because that's not going to change so all the way from aa6 to ad and all the way down let's clear anything out any prior results here so we can do that here a a six through a d and then just use the large number nine nine dot clear contents dot clear contents not the formats of course just the contents okay so once we have that we can now focus on the transaction database now what are we going to write inside there so let's bring this up here and.
Then we'll go into the transaction database now transaction transaction database and start working of course we're going to run that advanced filter based on the last row we know our criteria here results are going to come right in here the results only in x3 then i'm going to determine the last row and then i'm going to take this form and bring it down to the last row so that's what we're going to do and we're going to do that right in vba so let's write that up so the first thing of course is i want to clear any contents again i want to make sure that we've cleared any anything that might have come in here so let's go ahead and clear out all the way from x all the way through.
Let's just say a a clear anything out a x3 all the way over to a3 so let's do that so dot range x 3 through a a and then just use a large number and then we're just going to clear those contents out dot clear so clearing prior results we always want to do that okay once we've done that then what i want to do is we're going to set our last row last row is going to be equal to are of course column a that's going to get us our last row if we don't have any data we need to exit out if the last row in this case is less than three then exit sub so assuming that we do have there then we can move on now what do we want to do well we want to run our advanced filter so.
Advanced filter and it's going to be based on this i use auto hotkey for that just in case you didn't know but you know now because you're on the 20th episode okay so again our we want to run it based on our headers where is our headers located our headers are located right here inside a3 we need to make that less than 4 actually so our headers are located right here and then what i want to do is go all the way to l and then that's going to give us in this case l is fine so a3 to l so a3 in other words less than 4 right because it could be there four i want to make that four our first one is going to be on four all right so a3 all the way to l in the last row.
And then where is our make sure the last row of the variables correct last row then our criteria is going to be all the way over here based on that criteria remember it said an income first so it's going to be based on s2 all the way through u3 that is our criteria here so change this to s2 all the way through u3 and what do we want those results to go i want those results simply only in one is x2 that's where our results are going to go that's all i want to do so keep this keeping it simple x2 and we do want unique records so x2 that's going to give us our unique.
Categories based on those dates based on those dates only on those those dates then i wanted to determine the results of what are the last results row but i only want to do the last income rule i want to know the last increment i want to keep the income and expenses separate so the last income row is equal to in this case we're going to use column x so column x and of course i want to know do we have any maybe we don't have an income so we need to test that the last ink row is less than three so if the last income row is less than three then we need to skip bringing down those formulas then go to let's call it no income and then down here we brought in no.
Income but before that assuming that there is income we can then copy down those formulas okay so what is that formula well it's simply bringing basically y 3 all the way down to the last row is equal to y 1 based on just the formulas formulas not values so dot range y 3 through y in the last income row and the last income row dot formula not values dot formula equals dot range and then y1 our formula is located in y1 and that's going to be the formula dot formula.
Okay so that's going to be income formula now so now that we have that now we can focus on the expenses okay so what are those expenses so now we could use run another advanced filter so i'm just going to basically copy this right we've already got the last row and then i'm just going to make the updates for expenses so i'm going to paste it down here and then just make the updates accordingly okay so we have the last row but our criteria is going to change our criteria is now based on expenses so here it's going to be t2 through v3 t2 through v3 that's where we're going to based on these types i only want those.
Expenses so our criteria is going to change slightly so we can update that so t2 through v3 and our results are going to be in a different our results are going to be located in z now z that's where our expenses are going to i want those expense categories here in z2 and again the last row is going to be based on what's in z here not x then if the last in this case it's going to be last expense row we want to keep those variables separate so last expense row and if the last expense row is less than 3 then go to no expense something different here and bring that down here.
And we're going to go bring it down here called no expense so we skip that and then updating the formula this time our formula is located in a a for expenses it's a a so we do that aa last expense row not income in this case and then also it's going to be aaa and then we'll just change the memo and this is called expense formula okay so now we have it all expense formula good i like that we're pretty much done with that now all we need to do is just bring that information over through vba bring it over through and start looping through it so we can.