Generate Unlimited Report Cards With This Amazing 1 Click Dashboard [School Manager Pt. 20 FINAL]

hello this is randy with excel for freelancers and welcome to the school manager part 20. in this final training of incredible series i'm going to show you how you can create this incredible single click dashboard complete with profit and loss a student attendance report and report cards in which you can create a single report card or you can generate all report cards in a single click it's going to be an incredible training i cannot wait so let's get started 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 look at that so how we going to do that well the first thing we already have a named range called terms here if we look in the formulas name manager let me go to let's bring it over here we could click in terms we have the terms and it's based on this it's a dynamic based on these terms here so that's what i want to create a drop down list on that so that's the first thing what we're going to do inside our dashboard screen is create a drop down list so from the developers tab what i'm going to do is i'm going to insert and i'm going to insert here this particular combo box here so i'm going to put it right about here because that's the first thing what i want and i want it based on the terms so if i right click here format that control what what is the input range i want i want to put make sure that that is equal to terms here 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 do that okay so now that we have all of our income and all of our amounts here what i want to do is take this information bring it directly inside the dashboard i want to place it right in here so starting with ab6 and all the way down i want to bring in that but i only want to make sure that there actually is income if there's no income there's nothing to bring over so we do need to double check that so going to say here inside here starting down right here if the last income row is greater than 2 then do something then what do i want to do then dashboard we want to bring that information over so here dashboard range starting with a b 6 all the way through a c and the last income row plus three while we're adding three because we're starting on row six and here we start on row three right so there's a difference so we're starting on row three in the database for our transactions but six on our dashboard so we need to compensate for that difference dot value equals where's gonna equal it's gonna be basically all of our income here so we go into the transaction database equals x3 through y in the last income row so we just need to update that equals dot range x 3 through y and the last income row dot value so it's going to bring over bring over income details good so that covers our income okay but what i want to do is i want to keep track what row we're on i want to know what row we just brought it over all the income but i want to keep track of whatever row we're on so i need to have a variable called dash row and that's going to keep track of that row so let's update that and of course it's going to be basically starting on here plus our last dashboard so we need to update that so let's let's put in that variable right now so bring it over here so the dashboard row dash row is going to be equal to and this gets the last income row plus four and then we'll set the data that's going to change as we go through our dashboard row okay continuing on now that we have that what i want to do is i want to put in the total income now we've just put in all the balances but i want to right here i want to put in the word total or wherever whatever row total income and then inside a d i want to actually put that total income so the text here total income and here um i want to put that amount so that amount of course is going to come directly from here we already have that calculator here to make it easy for us in ab2 so let's put in the text and then we're going to do that so dashboard dot range a a and the dashboard row dashboard row dot value equals total income make sure we put the equals there okay so that's going to be our total income tax but where now what i want to do is i want to place that amount that total so where's that total going to go that total is going to go inside a d and where is it going to come from it's going to be coming from the equals right here a b dot range a b 2 that's going to be our total income amount total income okay good i like that now we need to do is we need to increment that dashboard row so the dashboard row equals dashboard row plus one we do a line though now i want to place those expenses here right so now what i want to do is i want to copy this here paste it here but now i want to put in expenses so instead of total income expenses we want to know those expenses it's going to be a we've already incremented the row here so that's going to cover that and then what i want to do is add in those expenses so we're ready to do that we can increment the row one more time because i only want one single row for the expense just the word expense just like we have here inside our desk we have one for income now we've listed the total income now i want to put the word expense i want to put our expenses down here and then total expenses and then total profit here okay so let's go ahead and go back in there and let's write that up right now in just a few lines of code just want to make sure that we actually have that that we have expense so if the last expense row just like it is greater than two then we're ready to write then dashboard dot range gonna go and say a b where is it located and the dashboard row we've already incremented it one so that's good and colon ac is going to come through there and we got to get the last it's going to be in this case and the last dashboard row plus what plus the last expense row last expense row minus two and that's what's going to go and i'll explain why a minute dot value is equal to and then our expenses are coming from z3 dot range z3 right above z3 all the way through aaa and the last expense row while you see how our expenses start on row 3 so we need to subtract 2 to determine the total number of expenses in that row and the last expense row okay excellent we've got one more row to compensate for inside the dashboard so let's make that minus three let's check out i think that's going to work fine all right so good now where we've added all in the expenses so now we just have to add in total expenses and the amount so i'm just going to copy this here because we did something i'm going to drop this down and then i'll just update it for expenses here so in this case aaa and the dashboard row we need to increment the dashboard row before we do that so we want to make sure that we're keeping consistent with the dashboard so what is the dashboard row going to be it's going to be the dashboard row is going to be equal to here let's put this in dashboard row this is going to be our last one right here so this is what our is in this case it's minus two in this case so our dashboard row updating that dashboard row based on all the expenses is equal to here the dashboard the last expense row plus the dashboard row but this is the case the minus two this is where i want to subtract two so this is gonna update our dashboard row here but i wanna subtract two so we have one in this case our next is total expenses i wanna put in total expenses and where's our expenses coming from it's coming directly right here from ac2 that's where our total expenses are so ac2 and just update this text total expenses okay good so now we have that now all we're going to do is increment the dashboard row one more time we want to put in that total profit so paste that in here and copy this again here in this case we can copy and update that this time we're going to have profit we'll just call this profit and where's our profit it's going to come it's going to be basically simple it's going to be our total income minus our total expenses so just right here all we need to do is just add that in here so based on those it's going to be in this case a b let's copy this here and then subtract a b from a b a b total income minus our total expenses that's gonna give us a total profit total profit okay i like that relatively easy we do need to add in some additional conditional formatting to make it look good but let's take a look at that and we'll run that okay saving our work we'll check for any bugs here and then we can move on okay so let's see we've got fees 400 we've got supplies salaries 400 that looks good total income 400 total expenses 270. let's go into our dashboard see how that looks we've got income fees at 400 i like that total income 400 correct expenses salaries and supplies good total expense is 270 profit 400 minus 270 130 okay so everything looks good we do need to get some conditional formatting and it's kind of an ugly port so why don't we do that now all right so the first thing what i want to do is i want to bold put bolt anything that's in our left column here i want to bold the entire line so i'm just going to go all the way down here i'm going to add in conditional formatting let's drop this pin this for a second conditional formatting new rule and then what i want to do is use a formula and basically i want to use anything a5 but making sure within anything in that in that column so we're going to remove the absolute from 5 does not equal empty so in that case i want to just basically start out and bold it so i'm going to put bold click ok ok and then we'll just apply it okay so that i like that so that way everything is zero but what else do we want to do let's let's uh add some more rules on that so we can see well i would like to give at least some lines for our normal expenses so how do we do that or normal income or normal expense of some lines so that's going to be anything that where a b contains a value so we're going to add a new rule in this case use a formula in this case we're going to use n is starting we're going to start in here a b 6 does not equal empty does not equal empty in this case i want to give it just probably let's say a top column and format that and then we'll assign that a border let's go with a dotted on the top and click okay i do need to set the applies to on that though click ok and this get i'll apply it use the same applied range here applies and click apply okay see how that looks now i also want to remove the i don't really like in this dashboard i don't like this particular grid line so let's remove that so we can get a better look at it okay so we need to update that so we've got salaries but i want it on fees too so we just need to update that so back in and then conditional formatting will manage those rules take a look at this uh take a look all right so we have here ab1 i don't want that i wanted to make sure it's starting on five so edit that and when we change when we click the applies to it changes automatically which is okay so now apply that and take a look at that that's the way i want it okay good so i want to do some more things i want to add in a solid line under or above the totals right if it's got total i want to add that in so how are we going to do that okay well anything with the total is going to be located in column a d so we can use 80. so i'm going to set a new rule this is a formula this one's going to be based on anything in ad5 right if ad5 does not equal empty then i want to put a line above that right so how we do that does not equal empty and then in this i want to format that here putting that line above here clicking ok and then also we do need to make sure if we check the apply sheet let's change that to 40 keep it consistent with the others all right let's take a look at that i like that so now we've got it there one more thing i want to make sure that anything that says profit has more maybe let's say put a gray background on it so anything that we're going to focus on the text right so now i want to use the find formula so i'm going to add a new rule into that this time i'm going to use a formula and we're going to use the find format because i want to know if it's if the word total is found if that word total is found anywhere or let's just say profit and we're going to use profit so equals is error i'm going to use is error i'm going to look for find we use the find command and what am i going to find i'm going to find the word profit i want to look for that if it is found i want to know so where is it going to be found it can be found anywhere in any row starting with aa5 so click here and any row meaning so we need to remove that absolute a a get rid make sure that's a there we go okay so if it's found right then great so if it's equal to false i want to make sure equal to false only when that happens so that means there's no error if there's an error nothing will happen if there's no error that means it's true right so format that and here's what we're going to do i'll give it a top and bottom border and i will give it a fill of let's say that gray and click ok so then click ok and now we'll just adjust the applies to and make sure it's working change this to 40 just as the others are and then apply and there we go click okay so now i like that okay so now we got a great that's exactly the way i want it but how do i bring this in aside here i want to bring it here so that's all we need to do is write in the macro so saving our work and we'll continue with our macro and just a few more lines of code on this profit and loss so we're done right now we're done with our transaction database so we're done with that so we're going to focus on the dashboard now so with dashboard everything else is going to come here i want to do a few things so first thing we want to do is i want to set the report range report range is going to be equal to starting in aa4 dot range a4 that is where our title is where header is right i want to start it right here aa4 and i'm going to go all the way to a d and whatever the last row is that's going to set our range and then we can bring it over so aa4 all the way through a d and we notice that in the dashboard row and the dashboard row wherever our profit is not dot value that's just the range that we're going to set okay so that's going to set our report range now all we need to do is copy that part range dot copy i'm going to bring that over information i want to put it directly where do i want to paste it i want to bring it right in let's say right here in f4 so we can paste it right there i want to paste everything i want to paste all the values and i want to paste the column headers too so we can use that so once we've copied it so we can dot range f4 is where we're putting it we're going to use paste special dot paste special first thing i want to do is we want to paste all so excel paste all the next thing i want to do is i want to put copy this and i want to paste those column widths column widths are unique for report so this one is going to be the column widths here so now we've pasted that and then i'm just going to turn off the cut copy mode application dot cut copy mode equals false okay that's it that's all we need to do and then what we want to do is when do we want to run this report so i'm going to copy this and i want to run this report any time the user selects on d3 so anytime you just select on d so that's a selection change event so we need to write that up right now so going back inside here i'm going to go look in our worksheet here worksheet events is where we're going to put it inside the dashboard now there's nothing here it's going to be based on selection change so here is where first thing what i want to do is if the if the user selects more than one cell target count large is greater than 1 then exit sub assuming that they don't what if they make if not they're gonna make a selection where we're gonna base it on gonna be base it on that's fine gonna be based on what d three all the way and down so let's just use d3 for now to d5 because we're only going to put in three reports so d3 through d5 is nothing so then what i want to do then we're going to do something else so then okay end of so here's we're going to write our code so the first thing what i want to do is i want to actually take off screen updating because that is going to help from flash so application dot screen updating equals false and make sure before we finish out the macro we turn it back to true if i can write false okay so before we end up for that end if here paste that in and make it true everything else will go in between there but the first thing i want to do is i want to clear out any reports i want to run a macro that's going to clear out all the information before we determine what report to run i want to run that macro so which macro and let's clear it out so from basically from f all the way through z or whatever however long we want to make it i want to clear everything out that might be there and then that includes all the formats as well and i'm going to use that in the macro so we can write that macro under our miscellaneous here so i've got a dashboard here miscellaneous module here and here's where we can write out we change some so this is the one i want to write clear macro so what is the range so first thing is we're going to do is dashboard it's very simple macro dashboard dot range this case f4 through z and then just use a large number in case you have a lot of data dot clear remember we're using clear i want to clear all the formats not clear contents but i also want to clear some information when i but clear this is for the formats but i also want to clear out also i want to know any of these if you want to clear these out too from b3 to b5 these are going to come in handy on the report card so b3 this is our selected report number selected student b3 through b5 just want to clear the contents of those so we can write that up here dashboard dot range b3 through b5 dot clear content okay so that's going to clear out the feed those just those fields that we need and then also what i want to do is i want to make i'm going to i'll create some shapes but we're going those shapes are only going to be used for report cards so we'll focus on that i'll write that in a little bit later but we don't need that now so that's it for the macro for now we'll add one more line of code later so i'm just going to copy that and we're going to go back into the dashboards here and i'm going to then let's pull it up here and then i'm going to paste that in here so clear report range so now how do we know which reports are run so let's copy let's get that report back to where we had i want to copy that the dashboard reports that's the one we've created i just want to copy that macro name that's the one i want to use but only if they select a specific row right only if they're selecting d oh excuse me only if they're selecting row three right so how do we do that so first thing what i want to do is i want to add in the row that they've selected and then i want to know so back into the dashboard here first thing i'm going to do is just put in the row i want to take that row and place it somewhere and where do i want to place it using conditional formatting i want to place it inside b3 here so how do we do that but it's actually be the row minus so it's going to be the row in this case 3 but i want to put the report number so it's going to be -2 target row minus 2. so take care of that range b3 dot value equals target dot row minus two set report number if the target dot row equals three then paste it in run p and l and as we write the two other reports we'll just add to that here and here okay saving our work and now we're going to run it take a look at that fix this here a d in the dashboard row don't that's gonna quotations are in the wrong spot there that doesn't help okay continuing on let's take a look at that okay that looks pretty good let's bring this down we don't need this this big okay i like that that looks very very good now what i also want to do is i want to automatically update this report when i change this so that's inside here so that's based on this report number so basically when i change the term i also want this to refresh so let's write that up inside the code right now based on that so inside dashboard miscellaneous here term change right if dot range b3 dot value equals one then refresh run the report so this one i want to run this one here right here this is the macro i want to run but only if the dasher report number is one so close this out paste this in here there we go so now it's going to run and we can check when there's no data saving that so here we go let's take a look here now when we change the term here it's going to run here okay we need to clear that out the first thing you know when we change the terms it's good but we need to clear we need to clear out whatever is there so let's just do that right now i want to make sure that we clear everything out so all we need to do is run the macro when we change the term i need to run the macro that's going to clear that what macro is that that's this macro right here clearing the reports just clear the reports placing that down here before we rerun that so changing the term here clears it out changing the term here clears it out and here but you see when we cleared out the report number that's not going to help us so all we need to do is instead of just all we really need to do is just use this line of code we don't need this so let's update that in other words we only want to clear the data not the report number so yeah that's probably going to work better okay so doing this and then this one here okay let's take a look at it now nothing here no data good i like that here data perfect all right the only thing i want to do is i want to select something else i want to select let's say f4 at the end of the report or at the hour all of them so inside our dashboard right here i want to select another cell so right here range f3 dot select and what that's going to do is going to so that it's not highlighted everything so now when i select this let's just refresh the term here here okay good so let's oh that's not going to work it'll work perfect so that's well let's do f4 let's do f4 and i want to do the same thing when we change the terms so f4 select and also the same when we change the terms i also want to do this exactly the same thing on term change here put that down here so now it'll work both on term change when we change the term i don't want everything selected term three and turn two okay great our profit and loss is done let's do the student attendance and then the report cards okay so how are we going to do that well it's just a little bit of a macro the attendance is relatively easy the attendance is going to be based on this database here so i'm going to drag the attendance database over here so it's close to the dashboard and also so what i want to do is again run a criteria based on certain attendance all of our attendance is going to come here the status of that attendance is here and i want to do it again based on the lesson date and the class id so we have some criteria here we have the lesson date based on those two days again from the date and two dates i want to run again another advanced filter based on this lesson date so all the lessons within that date range then what i wanted to get is i want to get those unique students the results of that is going to be the student name and the student id and i want to put that right here then what i want to do is i want to bring a formula and that formula is going to be located right here and i want to bring that formula all the way down here so that i know how many total present in that time how many total absent or how many totals sick now if you notice there's a formula here all we're doing is linking this directly to the admin screen so here inside the admin screen if you see here we have specific let's take a look at classes and lessons here so if i add tests here i want that automatically that attendance status to come up so our tendencies are dynamic as we add attendance here we have the test here so it's just linked there and all we did was use indirect based on this row number this is the row number notice i put these number row number nine row number ten row number eleven thirteen is the same as these rows numbers here 9 9 10 11 and all the way 13. so that's how we do it dynamically so that means anytime i want to count all the attendance data based on the data based on that information so inside the dashboard here i want to display all those here along with the column and i also want to have that information come through here so i'm bringing it all the way down but we need a formula i need to count how many of those student names but first what we want to do let's put some data in here before we put those formulas in here so we can use an advanced filter for that then i'm going to have all this information come over so i want the student name student id and all this brought into this so it's going to look something like this here inside our dashboard so i'm going to copy this here i'm going to place it inside our dashboard just the values here we're going to start it out in this case we're going to start at column b a b a so we're going to shrink everything else down here all the way down here because we're not going to use those we're just going to bring it so the next course report is going to be on ca so ba is where it's going to prove i'm going to put one report header here and then down here a sub header so i'm going to paste in those values here you can have the student name and the student id and then of course each individual here and and then i want a title here based on that so i want student attendance so i'm going to copy this title here i'm just going to copy this one here and then we're going to make the update so this is what i want to copy i'm going to paste it directly here and then i'm going to call this in this case it's going to be called student attendance so student attendant from okay so then of course we're going to send it out across the selection so i'm going to go all the way in this case all the way to probably how many are there let's take a look here i'm all the way to bq so i'm going to format these all the way to bq here that's what we want here and i'll give that a format and then also we want to sub format so actually our main format here cell styles here the table title then i want a subtitle a sub format here we have a cell style here for our sub and we're going to use that so it's consistent and then i'm going to drag this over and i'm again i'm going to center it across the selection that's the alignment that i want here center across selection click ok now what we're going to do is we're going to have vba bring this across here our student names are going to appear here our student ids and then the total number of apps in sick or whatever we want putting that down here okay so that's how it's going to work saving our work and we're demo of course once we have all the data we're going to bring it into our from our staging area and bring it into our the columns will be automated as well all right so let's do that right now let's write up some macros to help us with that all right it's going to be based on this attendance database so let's get that first part of the macro written so it's going to be a very very short macro it's relatively easy the formulas do all the hard work for us so inside our main macro here this is the one we want to go in through dashboard reports here now we're focused on the sub attendance report and the first thing we want to do again is clear any information from our current dashboard so what i want to do is in fact i'm also going to clear this header row because i'm going to bring this and the reason is because these are variable right these these could change as we change in our admin so i want to make sure i don't think that's the right one here let me just update that the cell style should be this one here yeah that's the one i want so these are going to change based on that so how do we do that well the best way to do that is just to clear start everything out starting with ba5 all the way through bq and down so inside our macro i'm going to do just that dashboard dot range ba5 all the way through bq and then long large row 99 dot clear the contents dot clear contents clear existing report data report data i can't type this morning okay continuing on now we're going to focus primarily on the attendance database with attendance database make sure we have the right okay that's the right name now the again i want to clear the contents in this case of any prior results so inside the attendance database here i'm going to clear out starting with 0 3 all the way through p i'm bringing those 03 all the way through p and down so let's clear that out dot range 03 through p 9999 clearing those out and then also what i want to do is m4 i want to keep our formulas intact i want to put the formulas here so what i want to do is keep those formulas here but then bring them down as we need them and then clear them out that keeps our workbook light so from q4 all the way through ae i'm going to clear out those two as well so q4 through ae q4 through a e clearing that range out too okay so dot clear contents clear prior results now that we have done that we determine the last row is going to be equal to same thing in this case we're going to determine last row based on column a and if the last row is less than three right our first row starts in row three here so if the last row is less than three we need to exit out of the sub so we can do that if the last row is less than three then exit sub so now that we know that we have data we can continue on i want to run our advanced filter so just like we do so often run that advanced filter this time it's going to be based on a2 all the way through j a2 through j keeping that up and then we're going to run our criteria is going to be based on here l 2 through m 3. so we can run that here l a 2 all the way through j criteria is going to be based on l2 through l3 l2 through l3 our results are going to come where our results i only want the student name and the student id so that's going to be o and p o two through p p two that's it so that's how we're gonna that's gonna get our results so let's run that now and see what we get let's fix that out not ap p2 running that okay good so we've got some we've got it here that's what i like student ids and names so now what we want to do is we want to focus on some formulas based on this right so how do we do that so basically what i want to do is i want to count how many present for this student id in this table based on these dates from from the from two remember these dates are based on our dashboard so it has to be between these two days between 419 and 625 that's what i want to count so it's going to basically going to use a countif formula so what is that formula it's going to be based on multiple criteria so equals count ifs multiple criteria and what's the first criteria the first one is going to be the student id right it has to be equal attendance can be based on that student id it's going to be based on what this particular id right here in p3 i want to keep that column i want to keep calling it's going to be dynamic for row so i'm going to keep the column p column p is always going to be there but the rows can change based on all the students so that's the first criteria what is the second criteria it's going to be the status i want to know what status the attendance status here this one is going to be based on what based on the status located right here this status again in this case our column is going to be relative but our row is going to be fixed so we want to make sure that our row is fixed so that's it so that's based on that now what's our next we're going to base it on the date the attendance lesson data attendance lesson date it's going to have to be there's two criteria it's going to have to be greater than or equal to the from date and the from date and it's also going to have to be less than or equal to two date attendance lesson date here is going to have to be less than or equal the two date and the two dates so there we go so that's the two date and perfect so that's all we need to do again so we want a student id making sure whatever's in the relative row and column p we want the attendance status in the relative column and the specific row of row two we want the attendance lesson date of greater than or equal to from date and the attendance lesson date then less than or equal to two day and hit enter so now we've got that so now what i want to do is drag that over here all the way over here and that's perfect so one absent one sick and as we drag now we can drag that down but we can also let vba copy these formulas and bring them down for all the students because we only want to bring the formula down for whatever students we have we can do that within vba so let's write that in the code now now that we have our formulas set up so inside that first of all i want to determine the last result row so the last result row is going to be equal to based on whatever is in o or p either one equals xlr okay copy that and it's going to base we'll use p because i like those fixed and that's going to determine if for some reason our last results row is less then get rid of that extra dot less than three three in this case we can't can't move on so we can do this this case i'm going to copy this and just change out the variable on here last result row okay so making sure that we actually have results now that we know we have results what we want to do is then we can copy those formulas down there so how we're going to do that it's going to be a simple formula to formula or in this case copy and paste so we can set our range what is our range our ranges.range q3 all the way through ae3 q 3 through a e3 that is our range that we're going to copy and then we're going to paste special paste form a copy and then we're going to use paste special facebook.range cube pasting it into q4 q 4 through a e and the last results row and the last result row dot paste special and then paste those formulas so paste formula is going to be down here all the way down to here okay so you can't see it's off the screen paste formulas so we're pasting the formulas and then of course application dot cut copy mode get rid of that equals false we don't no longer need that okay so after that copy mode so we've copied all the formulas now we're ready to bring it inside the dashboard so we have all of our data right here now i'm ready to bring it in but i want to include this one remember we've got some dynamic i want to make sure to include o2 so inside our dashboard we're simply going to create one that says ba five all the way through bq and the last results we're starting if this one starts in five and this one starts in row three we need to compensate two of them so we can do that with one line of code so starting with dashboard dot range ba5 through bq and the last result row plus 2 dot value equals dot range o 3 through a e in the last result row and the last result row bring over data bring bring over data okay so now we've brought it over into the dashboard we're good to go so let's end with that's it for our attendance database everything else is going to be with the dashboard so with dashboard now we can move into that so what do we want to do again i want to set the report range what is that report range going to be it's going to be based on here starting with ba4 all the way through bq and the last result row plus two plus two okay so let's go ahead and add that into the code right now dot range so we're gonna set the report range is gonna be equal to dot range ba 4 b a 4 through b q and the last result always based on the last result bro last result row plus 2 just as we did before plus 2. okay so now we've got that and we can continue on so all we need to do is just copy it it's not dot value it's just a range so now we're just going to copy the range just as we did before we did it almost exactly so all we need to do is just copy this what we've done before nothing else is going to change on that so just paste it right down here so that's it let's continue on and then copy that okay saving our work and we want to when do we want to run this report i want to run this report when they select that specific in two instances when they change the date range or when they select on that so those are the two instances we're going to run this report so let's add that macro to those two instances the first of which is inside the dashboard on screen when doing on the selection change right here so selection change in this case pasting that and we just need to copy this paste it down there and change it to four if the target row equals four then we're going to run the attendance run attendance report so all we need to do is just copy this here then put a space in there and copy this now what i want to do is run this based on when they change the term exactly so we can actually just copy this and then update the term when the term change we also want to run it that's in the dashboard miscellaneous here's where we want to run it here this case if dot range b 3 equals 4 you can imagine equals four then we're going to run it then run the dashboard report then run this put the then in okay good so now we can run it both on the this should be two not four row four this report number two okay so now when we click on the student attendance let's take a look at that i like that we just need to bring it out more so that we can have that make it look better here so we can bring that add in this here and then add in the colors here we need to update the color that's looking good alrighty let's take a look at that i like that wait looks now we've got our attendance the only thing what we need to do is we need to add some conditional formatting in here so it looks a little bit better and we added inside here so let's highlight all of these and go all the way down and add some conditional formatting i'm going to add at least probably two rules in here one for both odd and even based on whether there's data so it's going to be an and so conditional formatting i'm going to go into manage rules then i'm going to click new rule and the first rule is going to be based on and so equals and what are the two conditions one there has to be a value in ba and six but not it could be dynamic based on any row does not equal empty and the other one is going to be based on odd or even row so i'm going to use automate automate that it's going to the first one is going to be based on the even row and based on the even row i want to color it in this case let's color the gray so i'm going to format that and also give it some borders i'll give it this lighter gray color and i'll put a border all the way around it click ok now i'm going to copy this because i want to do almost a similar for the odd rows click ok and then oh let's fix the error there one more quotation there okay click ok and then new rule and then i'll have to fix that paste that in also fixing that quotation mark here this one and then make sure this is for the odd row so this is going to be one formatting those i'll keep this white but i'll change the border here clicking ok and click ok and then click apply there we go i like that just update this column i'm going to have to this column is going to be but we got our headers wrong we need to include the headers remember the header is not quite right so let's update that from there so we want to make sure the headers come all the way in from so back into the attendance database i want to bring these of the headers in so starting with o2 all the way down so let's update that inside the code so closing this one out must come from o2 here close this one out here this is what i want to do so ba 4 right here o2 it's got equal o2 gotta equal those headers okay so let's run that here and take a look at that here inside the dashboard here there we go now we've got those headers correct now we've got everything we've got conditional formatting correct just the way i like it now we know how many times they're present or absent or whatever perfect we got the student id here nice i like that just the way it looks okay great our last one is the report cards now i want to run the report cards let's make sure that this one updates on the term perfect that looks good so nothing no data and term perfect okay so that's looking good last one is the report cards what i want to do with the report card is i want to be able to print those report cards so i'm going to have two buttons for printing and i do have one in the scheduling let's copy a button here that i've got here the print button we don't need to do this so i'm going to just going to copy this here and this here and i'm going to copy that i'm going to bring that inside the dashboard here this one and then i want two buttons basically instead of i want to print the first one i want to do is i'm just going to print a selected report card and the other one is i want to put all report cards so we want to have two buttons based on either one so the user can choose which one they want to do so let's write some macros for that right now so first one is going to be print selected report card and then we'll increase that up and we'll give that a better width here formatting is probably about two inches there okay that looks good and i want to basically duplicate this and then so i'm just going to duplicate this here and there's going to be all report cards so this one's going to be all report cards so we're going to loop through all the students and this one will set it to about 1.7 here okay so i like that and we'll group these i only want to display these for the right report right so i want to group this all these here group them together and we'll call this report card group and of course when we group something we need to make sure that we're not sizing that so inside the properties move but don't size okay close that okay so we only want to display this group for a single report so i'm going to copy this and i'm initially we're going to hide it so back inside our miscellaneous this is what we wanted to do dashboard i'm going to hide it for all the reports and then show it for in this case dot shapes this shape dot visible equals msl false so we're going to initially hide it and then inside our dashboard report we're going to show it so that's the co report we're going to it's relatively easy so let's go ahead and do that dashboard reports this is the one i want to show it here but i want to do so the first thing what we want to do is i want to display it so equals let's update that equals mso true okay so we're showing that going to show that just for that report and not the minus let's get rid of that so this is going to be four different macros but there are three different macros now the first one is simply going to display all of our data here so let's clear it out it's going to display what i'd like is like a list of students and then in a report card here so then what i want to do is i want the user to be able to select on a specific student as you saw there in the preview initial then i want that report card data to show up based on the selected student then i want to give them the choice they can print that single report card or they can print all report cards so the first thing we want to do is just in our staging area again design that screen so we're going to go all the way to again starting with ca here and we're going to design that so i'm going to bring these and we'll design that it's relatively simple keep it simple today so what i want to do is basically have the student name and the student id so and then a header so let's go ahead and put i'm going to copy this header here in fact we can probably copy both of those and then we'll bring it into ca and then we'll make the updates accordingly so ca4 is where we're going to start it out pasting that so the first thing is i want to show report cards from change this text to report cards from and everything else and then the next thing i want to have the student name that's good the student name and the student id so it's fine the way it is here so we can just copy this to because we're using the same so i want to list out the student name and the student id here so all right so we'll expand this a little bit not that far because we don't need that but we'll merge and center this so this one's going to go all the way over we'll probably use a certain amount of columns because i want to display the report card so we're going to go all the way to ci here all the way to ci so this is what i'm going to format to our main title then i'm going to stretch this basically going to center across the selection with this one here you get it by now click ok so i want to have a student name the student id i want to have one space here and then i want to have the report card some information here so i want to also have a dynamic header based on the term so it's going to be equals creation report card and then dash and then what are we going i want wanna have the term name when we have that term call it term and then i wanna have the term name space and then where's that term name remember we placed that term name already here we placed it here so we can click on that okay so now we have so our report card is going to be here all the information a report card we'll just call it report card it's going to be a single report card report card and we'll give that uh some let's say i'm also going to give that this one and i also want to center this one across selection because this is going to be our report card so center call selection okay so inside here i want to have the student name inside that report card and then i'm going to put that student name here whatever that selected student name will be located in ce6 after that i want to have the student id and then after that i want to have the final score final all right perfect and then i want to have some headers here so and then i want to have a table here of all the classes that they took so here is going to be called the class name next up i want the teacher what who taught that class i also want the subject of the class and next up i want to have the dates from and then two and then i want to have the score the average score of all the the tests or quizzes or exams inside that for that specific class all right that's good enough let's give that the bold font and put borders all the way around it and then these were going to give it a specific color that gray color that we're going to use that for all right i like that so final score here we can keep those center these one these are going to be our headers so all of our all of our specific classes all the classes that it's been enrolled in for that specific period i'd say the word specific way too much i know that okay so let's do a border outside borders just on here that's what i want to do outside borders on here and here giving black column okay good i like that that looks nice that's gonna a little bit more space for the name that's all we need to do so then we're gonna have a table now here as we select the student names i want all their classes to load up i want their name and everything else to appear here good so how are we going to do that well again it's going to be based on the attendance database this database has all the information we want it has the scores we have the classes class id the lesson it has everything we need so we can use this in an advanced filter based on those dates here so i've got it here to help us out report card criteria i want to base it on the lesson dates i want to get all of the students bait inside that based on that then what i want to do when i select a student we don't need this here that's from the sample so i don't need that link here get rid of that formula so going to be based on dashboard b5 b5 is where we're going to place it on put that all right good so if there's a student id and b5 of the dashboard that's when we select a student this is where it's going to come in selected student row is going to be here the id that they've selected is going to be located in b5 something select one i want that automatically to come in the tenants database i want this to be here so we'll put the student name here and the student id and the results are going to come here so the student id is going to be here so this is then so then what we have is our criteria based on a selected student then i want all the classes to come here i want all the classes that that student was enrolled in based on those dates then we're going to have some header information here to have all that what header information we can have here it's the same as we placed inside this dashboard let's bring that over here it is right here this is the header information class name all the way here so i'm going to copy this here and i'm going to paste that inside there so inside here all we need to do is intensity is paste that in here so the formula so then we have formulas that are going to bring in all the class name based on these class ids i can bring in the class name i can bring in the teacher subject from to all of that then what i want to do is get an average score so we can do that with some basic formulas we've been over these formulas before so basically we're going to class name index based on this class ids so if i have a class id here everything's going to save so all we need to do is put in the class ids take these formulas and bring them down here based on this so we have again i've already filled this in because they're relatively simple the teacher based on the match of that class id we're going to have the subject based on that class subject the from due it's going to be based on the class from date and then the class 2 date and then i want an average score and i'll go over this formula with you in just a moment but let's start bringing in this what i need is all the first thing what i want to do is i want to know all the students that had classes between these lesson dates so all those students in a unique list so let's write that up right now inside our macro and that's going to be this marker called display students we want to display those students the first thing we want to do with this marker again is clear any prior results inside the dashboard so starting here inside the dashboard again ca6 all the way through c b 99 i want to clear all those so let's do that right now inside the macro so dashboard dot range ca6 through cb 9999 dot let's put another 9 in there dot clear contents clear existing students but i also want to do more than that i want to clear the classes if there's been any classes starting here all the way in cd8 through c i i want to clear any of the class information for that so let's do that also add in that comma cd8 let's bring this up here cd8 all the way through ci clearing that out eight through ci 999 clearing those contents of clear existing students and class details okay so once we have that we can then focus on the attendance database so with attendance database dot first thing i want to do is get clear any again clearing any prior results inside our attendance database also am through a n starting there and also here i want to clear out anything else here too so we can do that with just this line of code range am3 through a n and then 999 bring out also what i want to do is ap4 through av ap4 through a v and large row i'm going to clear out those contents so that's clear clear contents clear prior results once we have that we can then run our advanced filters so of course it's going to be based on the last row which we've already defined as a variable and it's based on l so we're going to ready to run if the last row is less than three i think i had that automated i can't remember the shortcut last row is less than three then exit the sub so assuming we have data moving on running our advanced filter we've been over this a lot of times in this series so this is it okay now we're going to run our advanced filter and here our advanced filter is going to be basic basically all i want to really need is the student names and the student ids in here so we really only need to run it to a2 through ad because that's all we're going to need but if you're going to add more later you can run it all the way to the last but it's only necessary until you get to them so a2 through j in this case i'll just put all of them in case we know a2 through j make sure our named variable is correct last row our criteria in this case i only want it based on those less than the dates for now only those lesson dates so those are located where are they located all the way over here i'm going to keep these a i through aj we're not running one based on the student i don't want that now just all the students ai through aj ai2 through aj3 a i 2 through a j 3. that's sufficient for now and then those results where those rules are going to come in am a through a n that's our results bring in there now what we want to do is we want to make sure that we actually have results row so again last result row is going to be equal to in this case we're going to focus on our student id which is in a n making sure that we have results for last rows if last results wrote less than three the negative sub doing the same thing here but just changing the variable here pasting it here last result row okay so now we know we have data and we can continue on so what are we going to do with that data well i'm going to bring it inside the dashboard so i'm going to bring it right in here so ca6 through cb and down that's we're going to so star dashboard.range ca6 through cb we're starting off in six and the last result row plus three we're starting in three in the result plus three dot value is equal to dot range am3 am three through a and and the last results row all right so now we have that we brought it over everything so that's it as far as the attendance database now we're going to focus on the dashboard so pretty much all we need to do is just copy this here and focus on that pasting that in here we can bring this here and bring this we've all dot shapes okay good it's bringing that shape in here we're going to set the report range we need to update this report range what is it going to be our report range i want basically all of starting with ca4 all the way through ci and down so let's see because we're just bringing over ca4 through ci and just we can just use the large row in this case ci and clearing that out this is going to be a fixed range but it's going to large row that's fine copy that paste perfect okay and then making sure that those things apply that's it for this part of the macro so saving that again now all we need to do is just copy this and create add this into the two instances where we want it the first one of course being on the dashboard screen here based on target row equal five so if target dot row equals five we do need to add some conditional formatting in there then run this report run display students okay so let's just make sure it's working and then also again might as well do it also on this then and then our of course our term change which is located in the miscellaneous here dashboard miscellaneous our term change here adding it here to if dot range b3 dot value equals three then we're going to run that also okay so running it on term change saving our work here and let's see if we've got any issues here and we can fix them accordingly report cards okay looks good we need to update this we just we need to make sure that it is fixed is the absolute right so b7 is not going to change that looks fine all right running it again that looks good okay conditional formatting is needed here and it's needed here so that's important those two things are needed uh looks everything else looks good let's drop moving everything else looks good so the idea is when i select a student i want all this information to fill in then they can either say here's our this is what i want here's our buttons that appeared here automatically for this report only so that's what we're going to do so let's add some conditional formatting relatively easy in here in fact i can just copy this conditional formatting that's already here and paste it right in here here and then update the code so what we're going to do is just go back in there and then update the rules on that manage those rules the two rules that we copied over so we like this b this case is going to change to c a6 right c a6 that's good click okay and then it applies to it's going to be larger range here all the way down here just change it to 999 and then click apply and make sure this doesn't change okay we're going to do the same thing here in this case ca6 here click ok the applies to we're going to keep this change it to here apply that okay good i like that there so now we've got that and i'm going to do the same thing for here i want also the same thing so i'm going to copy that paste it down here then conditional formatting manage rules we're doing exactly the same thing just updating the conditional formatting this case we're going to focus on cd8 so cd8 click ok and then the applies to in this case is going to be all of these here all the way down here and then all to a large row here okay click apply make sure that doesn't change again doing the same thing here cd8 click ok and then you have copy the applies to here paste it down here and then click apply ok good i like that click ok but in actuality what we want to do is probably and that should be fine it should be sufficient for now i like that as soon as we as we add data here right the conditional format perfect that's what i want that'll look nice i like it okay deleting the data all right saving our work so now we've got our conditional formatting making sure when we bring it over our conditional formatting is now brought over now all we need to do is add one i want to add one more rule on conditional formatting when i select a row i want that selected row to highlight so we have selected student row here b4 so let's add one row but we need to add it to our original here so highlighting that conditional formatting manage rules here new rule in this case formula in this case b4 equals selecting on b4 is going to equal to the row e4 equals the row when that happens then i want to format that what kind of format do i want i want to do a fill we use this color here i want the font of bold and i want to set it to white clicking ok and click ok so now when i bring that over ca that looks good let's take a look at that so now when i run the report again and then i change the row for a selected row let's say 8 then that row now as we selected so now when i select something on this report i want that road to show up here and i also want the student id so we can write that here writing that code okay so let's write up the macro so that we know when we select that so that's going to be on selection chain so we can write that up inside our dashboard adding on to that selection change so here inside the dashboard we've already got some selection chains but this is going to be based on a new range based on two conditions one they must select between f six and g and down also i wanna make sure that the report number that they select has to be report number three this is only for report number three and i also want to make sure that there's a value in f so we're going to check on those so starting in selection change f6 through g so here under selections change if not selection states f6 through g and down f6 through g999 nothing and range b3 dot value equals three we want to make sure it is is actually that specific report and also i want to make sure right we want to make sure that is the right report number only on that and i want to make sure that f contains a value and range f and the target target dot row dot value does not equal empty then we're going to do something so what are we then adding to then what are we going to do well then i want to do a few things i want to put that row that target row inside b4 so range b4 dot value equals the target row i also want to put the student id inside b5 remember i want that student id it's going to come from g and whatever the target row is so let's do that now range b5 dot value equals range g and the target dot row dot value student id all right i like that so want to make sure that and then we'll just check on that okay it looks good now it's changing over so now what we can do is if we know what student id we know the name all i need to do is start filling in this information but what i'm going to do is i'm going to bring in those formulas and fill it here so if i put it here when we bring over the formula is going to be brought over too so all i need to do is add in that formula so we can add it in here equals if air in case there's enough error let's add that in equals if error we're going to use an index i want to index what am i going to index i'm indexing that student name so student name here and then i want to index based on the row we're going to use a match we're going to match it on based on the student id where's that student id located it is located right here inside b5 now that we have the student id i am going to actually make sure that that is absolute using f4 then what i want to do is student id that's what we're looking at exact match first column right one column if there's an error use empty okay so that's going to bring over here but what about now we want to do is i'm going to add in that student id here that should be student id i want to put that student id of course that's going to be located right in just b5 right so we notice that b5 and make sure that that's absolute student id is going to be there so and then if we're making absolute because when we bring it over we need to make sure it doesn't change and i also want to know the final score right what is how we going to use that well it's going to be basically all the scores down here based on the average value so let's write that in there equals if air in case that getting used but i also want to round it i don't want more decimals than than let's say one or two it's fine so we're going to use the average i'm going to do the average of what we're going to use the average of basically whatever is located in all the way down here starting here and then just go all the way down to a large number so let's just say there's 25 classes or whatever it's more than enough so we're using we're going to get the average of that and i also want to know the number of digits one will do one and if there's an error we'll just do empty okay so i like that so that way as the scores appear here so does this so all i need to do is select on a specific student and have that appear here so if i select on it but when we load it now those formulas are going to load so if i reload it and now we select on it now we see that it's automatically here so we've got the student id here i like that let's bring this over here on the left here and then i'm going to right justify this this and this right make sure that those are right justified okay perfect so we've got the student spell that right we've got a letter there student id here and the final score the student name so now all we need to do is run a macro that loads the classes when we select a specific student everything else is looking good saving our work so we're going to run that macro right now we select a student i want to know all the classes and again that's going to come directly from this but in this case we now have to our student id remember as it's based on b5 so now i can run a macro that's going to load all of the class ids based on this student id here so we can do that with just a little bit of a code so let's write in here and that's going to be right inside here not here not a term change we're going to locate that that's display students so what i want to do is i want to generate report card generate the selected so we can do that with just a little bit of code so how do we do that well that's right here inside first again first what we want to do is always clear out any previous information here so i want to clear out everything from i 8 all the way through and and down inside the dashboard so dashboard dot range i ate through and and then a large number just clear out those contents dot clear contents okay next up we're going to focus with the attendance database just as we did before attendance database and we also very very similar so we can just copy this because we did almost everything else i'm just going to copy this here all the way here and then we can just make the adjustments here because it's very very similar but but we do have some difference so the first thing i want to clear the prior results inside our attendance database so what are those prior results in this case i really want to focus on here prior results ap all the way through ap4 through av so those are our those are ones where i want to clear out ap for it so just that in this case so clearing this out next up the last row is going to remain consistent of last row aj2 we'll bring it all the way through j and that's fine our in this case our criteria is going to be all the way to ak3 because we want to include that student id our results are going to come i only want the class ids right i only want to bring in the class of these our formulas will take care of everything else so just the class id which is located in ap3 that's all i want to bring over so a p 3 is where it is ap 3 is where we want the results to show up that's it so our last results are going to be located in this case on ap that's where our last results were if the last results row in this case is less than four then we know that there's no classes so four we can exit the sub out assuming that we do have data we can then continue on then what i want to do is i want to take these formulas and i want to bring them all the way down based on however many classes we have so we can do that if our first one is based on this all we need to do is use a formula to formula so we can do that with this so saying let's say dot range aq4 so our first row a q 4 through a v and our last results are a last result row dot formula not value dot formula equals in this case aq2 through av2 equals dot range aq2 also the formulas 2 through a v 2. also the formulas capitalize this make it look a little bit better properly okay so we have that now it's not the formula so it's going to bring down all of our formulas that's going to bring down the classes names and we're also going to bring down the scores so once we have that we can then bring that over into the dashboard so very very simple just as we did before in this case here no let's go ahead and go in this case i say that a lot too all right so cd8 through ci cd8 through ci that's what we're going to bring that's our destination so dashboard dot range cd8 but maybe we don't need to bring we can just bring it into the original destination why don't we just do that right we don't need to bring it into our staging area we can our conditional formatting has also been over so we can bring it directly in to i8 through n that's fine too so let's do that dot range i ate through n and what the last result row plus what well if ours if our results are starting in four and they're going into eight so we need to add four to compensate for that dot value equals dot range a aq4 through av a q 4 through a v and the last results row and the last result row it's going to bring over bring over class data okay i like that let's get an and sign here we need that and okay that looks pretty good i like that and when do we want to run this macro well i want to run this bits of macro when i select anything when i select run that selection change that's when i want to run it so let's add that macro to the selection change event here inside the dashboard right here that selection change event once we brought over that let's run the macro here can clear this up a little bit and take a look at this let's take a look at it fix this one here not a3 right obviously we don't want a3 it should be in the attendance database we want to make sure it's ap3 okay so we need to fix that here ap3 is where we want those classes to run here and then run that okay that looks good we've got our class information here our dates formats you know we don't need them formatted here we have some formulas i didn't go over this formula let me go over this one when you can so basically what i want to do is i want to run a sum if some if and then i want to divide i want to i want to know the total scores and i want to divide it by the number of scores so the first thing we're going to do is i'm going to sum ifs the attendance scores and i want to base it only on those with the specific student id there's that word again ak3 then i attendance class i want to know the class based on this class id and ap4 the lesson date must be greater than or equal to the from date the lesson date must be less than or equal to date and i want to divide that by basically how many different quizzes tests scores how many scores for that specific class so i want to know how many for that class id when we bring that formula in it's the average score of all the tests for this that class so we know that so they're bringing that formula down all right good i'm glad i got to show you that part of it so that's important and that's going to simply bring that formula all the way down once we have that we can then bring it back inside our dashboard right here so when i select on that it's going to bring it so now we just need to update the conditional from a they didn't get brought over so let's figure out why that conditional formatting didn't get brought over update that go in conditional formatting manage those rules and we see that it's going to be based on cd but what i want is on it based on the destination so we can do that let's what is that destination here and that destination is we can base it on the destination based on here located in i8 so why don't we just add in that destination here inside the conditional formatting so all we do is conditional manage those rules and change it to i8 and that'll work just fine i8 and the reason is is because we don't bring the report over again right we're not bringing it over again we're just loading it we didn't we didn't bring it back in here and then loaded and we didn't bring it back into our staging area which is fine so clicking click ok and then apply and then okay so now that's the way i like it alrighty that looks good now we reload the report here and reload that now we have our conditional formatting brought in so now when we select it notice that we have everything here our average score here all right that's really good now all we need to do is just either print one or print them all and that's just with two simple macros so let's get them written up and then i'm going to let you go on this final incredible training located here okay so we've got that now all we need to do is just print either the selected report or we need to print all of them in just two macros so writing that up here let's go ahead and write the first one here that's going to be very very simple it's going to be here we can just use dashboard dot page setup set up that page i'm going to set that print area printer what is that print area is going to be it's going to be equal to here dashboard dot range i5 is our first one i5 through n is the last column and what about the last row and what is that last row it's going to be basically based on the dashboard also dot ring dot range gonna use xlr i'm gonna use that based on here so all we need to do is just clear that out not on a in this case we're gonna base it on column i i wanna know the last result row based on i so i it's going to give us our last row dot all right so we need a dot address i want to know the address of that that is it that's going to be give us our it's going to set that printer then all we need to do is print out dashboard dot range dot print out print out what are we printing out well we're gonna print out we don't need to specify any from or to or copies or preview the active printer will use just the active printer print to file and then ignore printing areas and then false we don't want to ignore those okay so that's it so now all we need to do is assign this macro to that button that we used this print selected so this i'm going to send it to both the icon and the button itself right click them assign the macro paste that in click ok all right so we do need to update these dates we need to need date formats so we can do them here once we update those here just bring that all the way down set those general here to the short date and then i'm going to bring these out a little bit i mean both of these columns a little bit bigger okay let's re-run that here and then select on it now we've got our date formats here we've got our scores we've got our screen now we can print the select and let's take a look it's going to print out to my snagit which is my default printer and we'll see that it's been printed out okay good that looks good now all we need to do is just run a loop i want to do the same thing for all of them in fact we're going to start in this macro i'm going to start in f and go all the way down and we're going to just going to repeat it for all of those students so that's the last macro that we need to write so again all we need to do is just focus on the dashboard with dashboard and all we want to do is we need the last row i need to know the last row is going to based on f in this case f equals column f is what our last student is going to be located on that's going to set it in column f we can then set our last row making sure if the last row is less than let's bring this down so we can see both here if the last row is less than six and exit out that means there's no student less than six then exit sub assuming that we have students we can then move on set we can run a loop okay i wanted to mention the report we've already have the report row dimension all the way up here in our top here so report row here this is the one we're going to focus on to make sure i got that right 4 report row is equal to 6 to the last row close our loop next report row inside that loop what i want to do i want to set the specific student how are we going to do that easily we just select it dot range f and the report row just select it because when we selected that macro is going to run select the macro is going to run automatically selecting it now all we need to do is just print it out pretty much easy all we need to do is just run this here we don't need the word dashboard because we're already inside run that page set up here because the last row could be dynamic there could be more classes or less classes so we don't know set the print area here true or false and it's going to go for every single student so now we're just going to once we select it's going to load that data in here then it's going to set this set the print area then it's going to print out that's all we need to do very very simple very easy saving our work here and then we're going to assign this macro copy this macro to our print all button right click and here go inside here click on here and the icon we wanted both and then assign that macro pasting and click ok print all the report cards it's going to individually print every single one of them just like that very cool all right you see it's printing out now we've printed all the report cards wow awesome it's been an incredible training an incredible series i'm so glad for those of you who stuck with us and i really appreciate it it's been a long one i don't know how many really long ones i'm going to do i've got some incredible trainings coming up i got an updated pos i've got an automated invoicing coming up with payments it's going to be great training out so many thank you for sticking with us i really do appreciate that we'll see you next week for a brand new training thanks again you

Channel: Excel For Freelancers

Views: 9,231

Rating: 4.973856 out of 5

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, School Management App, School Manager App, Student Manager App, School Software Excel, Excel School Software, School Report Cards, Report Cards, Excel Report Cards, Excel Attendance, Attendance in Excel, Track Students, Student Tracking, Generate Report Cards, Excel Dashboard

Id: GrhFMQ7QoSs

Length: 108min 24sec (6504 seconds)

Published: Tue Jul 06 2021